How to deal with mysql on MAC OS

A long time ago I remember I installed MySQL on my MAC. Actually it is about Maria DB, but that’s pretty much the same thing as it was made by the original developers of MySQL database. Now I am in the position to start a new project with Struts Framework and I need a backend technology too. To start from scratch and re-install Maria DB it is not an option, as already have it installed. I just need to figure out where it is installed.

Therefore, my first concern was where is the service installed and is it started? To find out these two information I run the next commands in Terminal:

which mysql – This command responds with the location of the mysql (aka. maria db)
mysqladmin –version – This command tells the version of mysql. In my case it responded with [connect to server at ‘localhost’ failed] which indicates that the service is not started.

Go to the location indicated by the which command and run the commands to start, stop or restart mysql:

mysql.server start
mysql.server stop
mysql.server restart

Voila, my service is up and running in just a few minutes. Now it will be the time to check which version of mysql server I have. I run the next command:

mysqladmin --version

1	mysqladmin --version

Let’s not stop this post here, and continue with basic mysql commands.

Basic mysql commands to run in Terminal

To execute mysql commands from terminal you first need to start the mysql tool. For this, type mysql in terminal and press enter.

Claudius-MacBook-Pro:~ claudiu$ mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 10.1.14-MariaDB Homebrew

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

Claudius-MacBook-Pro:~ claudiu$ mysql

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MariaDB connection id is 3

Server version: 10.1.14-MariaDB Homebrew

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

For the next example I will choose ; to end the commands.

You might want to specify a username, and a host where to connect when starts the mysql tool. In order to accomplish this, type:

mysql - u [username] -p [password] -h [hostaddress]

1	mysql - u [username] -p [password] -h [hostaddress]

(replace username, password and hostaddress accordingly).

One note I wish to make here is that, by default, the root user has no password selected. You might want to secure the root account with a password by running the following command:

mysqladmin -u root password "yourNewPassword";

1	mysqladmin -u root password "yourNewPassword";

Check if your service is running

ps -ef | grep mysqld

1	ps -ef \| grep mysqld

List all databases

show databases;

1	show databases;

List all tables in a specific database

To list the tables from a database, you first need to run the command to use that specific database and next run the command to list the tables names. In order to perform this activity run the next two commands:

use [database_name];
show tables;

1 2	use [database_name]; show tables;

Misconception about mysql

One misconception about mysql is that it cannot handle tables larger that 4 GB. This is not entirely true as this is just the default file size limit for a table in mysql. You can override this default behavior and increase the default size, and in theory you can increase this limit upon 8 million TB (if your operating system can handle it).

Spread the love