MySQL – Administration

Starting MySQL Server:

On Linux (Ubuntu/Debian):

$sudo service mysql start
# or
$sudo systemctl start mysql

On Linux (CentOS/RHEL):

$sudo systemctl start mysqld

On Windows:

Open a Command Prompt or PowerShell with administrator privileges, then execute:

net start mysql

Alternatively open the Services App by typing “Services” on the Windows search bar. In the Services App, scroll down to the MySQL service on the list. Using your mouse, right-click on the item to access the context menu with the appropriate commands.

On macOS:

Open macOS system preferences and select the MySQL preference panel, and then execute “Start MySQL Server” on the appropriate instance.

Alternatively using the command-line interface:

$sudo mysql.server start

Stopping MySQL Server:

On Linux (Ubuntu/Debian):

$sudo service mysql stop
# or
$sudo systemctl stop mysql

On Linux (CentOS/RHEL):

$sudo systemctl start mysqld

On Windows:

Open a Command Prompt or PowerShell with administrator privileges, then execute:

net start mysql

Alternatively open the Services App by typing “Services” on the Windows search bar. In the Services App, scroll down to the MySQL service on the list. Using your mouse, right-click on the item to access the context menu with the appropriate commands.

On macOS:

Open macOS system preferences and select the MySQL preference panel, and then execute “Stop MySQL Server” on the appropriate instance.

Alternatively using the command-line interface:

$sudo mysql.server stop

Restarting MySQL Server:

On Linux (Ubuntu/Debian):

$sudo service mysql restart
# or
$sudo systemctl restart mysql

On Linux (CentOS/RHEL):

$sudo systemctl restart mysqld

On Windows:

Open a Command Prompt or PowerShell with administrator privileges, then execute:

net stop mysql
net start mysql

Alternatively open the Services App by typing “Services” on the Windows search bar. In the Services App, scroll down to the MySQL service on the list. Using your mouse, right-click on the item to access the context menu with the appropriate commands.

On macOS:

Open macOS system preferences and select the MySQL preference panel, and then execute “Restart MySQL Server” on the appropriate instance.

Alternatively using the command-line interface:

$sudo mysql.server restart

Checking MySQL Server Status:

On Linux (Ubuntu/Debian):

$sudo service mysql status
# or
$sudo systemctl status mysql

On Linux (CentOS/RHEL):

$sudo systemctl status mysqld

On Windows:

Open a Command Prompt or PowerShell with administrator privileges, then execute:

sc query mysql

Alternatively open the Services App by typing “Services” on the Windows search bar. In the Services App, scroll down to the MySQL service on the list and visually check the status.

On macOS:

Open macOS system preferences and select the MySQL preference panel, and then you can visually check the status of the appropriate instance.

Alternatively using the command-line interface:

$sudo mysql.server status

Alternatively for all systems you can use the MySQL command-line client to check the server status by opening a terminal or command prompt and running the the MySQL client:

$mysql -u your_username -p

Replace your_username with your MySQL username. Enter your MySQL password when prompted. Once logged in, you can check the server status by typing the command below to display the server’s uptime in seconds:

mysql> SHOW STATUS LIKE 'Uptime';

Setting Up a MySQL User Account

Connect to MySQL Server:

$mysql -u root -p

Create a New User:

mysql> CREATE USER 'new_user'@'localhost' IDENTIFIED BY 'user_password';

Be sure to replace new_user with the desired username, and password with the password you want.

Grant Privileges to the User:

mysql> GRANT ALL PRIVILEGES ON your_database_name.* TO 'new_user'@'localhost';

If you want to grant different sets of privileges or restrict access to specific tables, you can modify the privileges accordingly. For example, to grant only SELECT privileges, you can use:

mysql> GRANT SELECT ON your_database_name.your_table_name TO 'new_user'@'localhost';

Apply Changes and Refresh Privileges and exit the MySQL client:

mysql> FLUSH PRIVILEGES;
mysql> exit;

Backing up and Restoring MySQL Database

Backing up a MySQL Database Using mysqldump

Open a terminal or Command Prompt. For a single database backup run this:

$mysqldump -u your_username -p your_database_name > backup.sql

Replace your_username with your MySQL username, and your_database_name with the name of the database you want to back up.

Run this for multiple databases backup (useful when backing up multiple databases):

$mysqldump -u your_username -p --databases db1 db2 db3 > backup.sql

Replace your_username, db1, db2, db3, etc. with your MySQL username and the names of the databases you want to back up.

Restoring a MySQL Database

Open a terminal or Command Prompt and follow the steps to restore a database created using mysqldump. First, create a new empty database (if it doesn’t exist already):

$mysql -u your_username -p -e "CREATE DATABASE your_database_name;"

Restore the data from the backup file into the newly created database:

$mysql -u your_username -p your_database_name < backup.sql