{"id":125,"date":"2023-07-22T10:28:40","date_gmt":"2023-07-22T10:28:40","guid":{"rendered":"http:\/\/local.tutorials\/?post_type=topic&p=125"},"modified":"2023-07-22T10:28:42","modified_gmt":"2023-07-22T10:28:42","slug":"mysql-administration","status":"publish","type":"topic","link":"http:\/\/local.tutorials\/topic\/mysql-administration\/","title":{"rendered":"MySQL – Administration"},"content":{"rendered":"\n
On Linux (Ubuntu\/Debian):<\/strong><\/p>\n\n\n\n On Linux (CentOS\/RHEL):<\/strong><\/p>\n\n\n\n On Windows:<\/strong><\/p>\n\n\n\n Open a Command Prompt or PowerShell with administrator privileges, then execute:<\/p>\n\n\n\n Alternatively open the Services App by typing “Services<\/strong>” 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.<\/p>\n\n\n\n On macOS:<\/strong><\/p>\n\n\n\n Open macOS system preferences and select the MySQL preference panel, and then execute “Start MySQL Server<\/strong>” on the appropriate instance.<\/p>\n\n\n\n Alternatively using the command-line interface:<\/p>\n\n\n\n On Linux (Ubuntu\/Debian):<\/strong><\/p>\n\n\n\n On Linux (CentOS\/RHEL):<\/strong><\/p>\n\n\n\n On Windows:<\/strong><\/p>\n\n\n\n Open a Command Prompt or PowerShell with administrator privileges, then execute:<\/p>\n\n\n\n Alternatively open the Services App by typing “Services<\/strong>” 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.<\/p>\n\n\n\n On macOS:<\/strong><\/p>\n\n\n\n Open macOS system preferences and select the MySQL preference panel, and then execute “Stop MySQL Server<\/strong>” on the appropriate instance.<\/p>\n\n\n\n Alternatively using the command-line interface:<\/p>\n\n\n\n On Linux (Ubuntu\/Debian):<\/strong><\/p>\n\n\n\n On Linux (CentOS\/RHEL):<\/strong><\/p>\n\n\n\n On Windows:<\/strong><\/p>\n\n\n\n Open a Command Prompt or PowerShell with administrator privileges, then execute:<\/p>\n\n\n\n Alternatively open the Services App by typing “Services<\/strong>” 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.<\/p>\n\n\n\n On macOS:<\/strong><\/p>\n\n\n\n Open macOS system preferences and select the MySQL preference panel, and then execute “Restart MySQL Server<\/strong>” on the appropriate instance.<\/p>\n\n\n\n Alternatively using the command-line interface:<\/p>\n\n\n\n On Linux (Ubuntu\/Debian):<\/strong><\/p>\n\n\n\n On Linux (CentOS\/RHEL):<\/strong><\/p>\n\n\n\n On Windows:<\/strong><\/p>\n\n\n\n Open a Command Prompt or PowerShell with administrator privileges, then execute:<\/p>\n\n\n\n Alternatively open the Services App by typing “Services<\/strong>” on the Windows search bar. In the Services App, scroll down to the MySQL service on the list and visually check the status.<\/p>\n\n\n\n On macOS:<\/strong><\/p>\n\n\n\n Open macOS system preferences and select the MySQL preference panel, and then you can visually check the status of the appropriate instance.<\/p>\n\n\n\n Alternatively using the command-line interface:<\/p>\n\n\n\n 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:<\/p>\n\n\n\n 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:<\/p>\n\n\n\n Connect to MySQL Server:<\/p>\n\n\n\n Create a New User:<\/p>\n\n\n\n Be sure to replace new_user with the desired username, and password with the password you want.<\/p>\n\n\n\n Grant Privileges to the User:<\/p>\n\n\n\n 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 Apply Changes and Refresh Privileges and exit the MySQL client:<\/p>\n\n\n\n Backing up a MySQL Database Using Open a terminal or Command Prompt. For a single database backup run this:<\/p>\n\n\n\n Replace Run this for multiple databases backup (useful when backing up multiple databases):<\/p>\n\n\n\n Replace Restoring a MySQL Database<\/strong><\/p>\n\n\n\n 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):<\/p>\n\n\n\n Restore the data from the backup file into the newly created database:<\/p>\n\n\n\n <\/p>\n","protected":false},"excerpt":{"rendered":" Starting MySQL Server: On Linux (Ubuntu\/Debian): On Linux (CentOS\/RHEL): On Windows: Open a Command Prompt or PowerShell with administrator privileges, then execute: 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 […]<\/p>\n","protected":false},"featured_media":45,"comment_status":"open","ping_status":"closed","template":"","meta":[],"categories":[3],"tags":[],"subject":[16,17],"yoast_head":"\n$sudo service mysql start\n# or\n$sudo systemctl start mysql<\/code><\/pre>\n\n\n\n
$sudo systemctl start mysqld<\/code><\/pre>\n\n\n\n
net start mysql<\/code><\/pre>\n\n\n\n
$sudo mysql.server start<\/code><\/pre>\n\n\n\n
Stopping MySQL Server:<\/strong><\/h2>\n\n\n\n
$sudo service mysql stop\n# or\n$sudo systemctl stop mysql<\/code><\/pre>\n\n\n\n
$sudo systemctl start mysqld<\/code><\/pre>\n\n\n\n
net start mysql<\/code><\/pre>\n\n\n\n
$sudo mysql.server stop<\/code><\/pre>\n\n\n\n
Restarting MySQL Server:<\/strong><\/h2>\n\n\n\n
$sudo service mysql restart\n# or\n$sudo systemctl restart mysql<\/code><\/pre>\n\n\n\n
$sudo systemctl restart mysqld<\/code><\/pre>\n\n\n\n
net stop mysql\nnet start mysql<\/code><\/pre>\n\n\n\n
$sudo mysql.server restart<\/code><\/pre>\n\n\n\n
Checking MySQL Server Status:<\/strong><\/h2>\n\n\n\n
$sudo service mysql status\n# or\n$sudo systemctl status mysql<\/code><\/pre>\n\n\n\n
$sudo systemctl status mysqld<\/code><\/pre>\n\n\n\n
sc query mysql<\/code><\/pre>\n\n\n\n
$sudo mysql.server status<\/code><\/pre>\n\n\n\n
$mysql -u your_username -p<\/code><\/pre>\n\n\n\n
mysql> SHOW STATUS LIKE 'Uptime';<\/code><\/pre>\n\n\n\n
Setting Up a MySQL User Account<\/h2>\n\n\n\n
$mysql -u root -p<\/code><\/pre>\n\n\n\n
mysql> CREATE USER 'new_user'@'localhost' IDENTIFIED BY 'user_password';<\/code><\/pre>\n\n\n\n
mysql> GRANT ALL PRIVILEGES ON your_database_name.* TO 'new_user'@'localhost';<\/code><\/pre>\n\n\n\n
SELECT<\/code> privileges, you can use:<\/p>\n\n\n\n
mysql> GRANT SELECT ON your_database_name.your_table_name TO 'new_user'@'localhost';<\/code><\/pre>\n\n\n\n
mysql> FLUSH PRIVILEGES;\nmysql> exit;<\/code><\/pre>\n\n\n\n
Backing up and Restoring MySQL Database<\/h2>\n\n\n\n
mysqldump<\/code><\/strong><\/p>\n\n\n\n
$mysqldump -u your_username -p your_database_name > backup.sql<\/code><\/pre>\n\n\n\n
your_username<\/code> with your MySQL username, and
your_database_name<\/code> with the name of the database you want to back up.<\/p>\n\n\n\n
$mysqldump -u your_username -p --databases db1 db2 db3 > backup.sql<\/code><\/pre>\n\n\n\n
your_username<\/code>,
db1<\/code>,
db2<\/code>,
db3<\/code>, etc. with your MySQL username and the names of the databases you want to back up.<\/p>\n\n\n\n
$mysql -u your_username -p -e "CREATE DATABASE your_database_name;"<\/code><\/pre>\n\n\n\n
$mysql -u your_username -p your_database_name < backup.sql<\/code><\/pre>\n\n\n\n