[go: up one dir, main page]
Scribd
Upload
55 views2 pages

Give Root SQL Password.: We Have Backup Database and Mysql Database Users On Server

This document provides instructions for backing up and restoring MySQL databases and users: 1) It describes how to dump all databases to a SQL file using mysqldump and export a list of users to a text file. 2) It then shows how to convert the user text file to SQL grants statements and add semicolons to each line. 3) Finally, it outlines the process of importing the database and user backups to a new server, including creating the databases, importing the user SQL file, and restoring the databases from the SQL dump file.

Uploaded by

Lord_King
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
55 views2 pages

Give Root SQL Password.: We Have Backup Database and Mysql Database Users On Server

This document provides instructions for backing up and restoring MySQL databases and users: 1) It describes how to dump all databases to a SQL file using mysqldump and export a list of users to a text file. 2) It then shows how to convert the user text file to SQL grants statements and add semicolons to each line. 3) Finally, it outlines the process of importing the database and user backups to a new server, including creating the databases, importing the user SQL file, and restoring the databases from the SQL dump file.

Uploaded by

Lord_King
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 2

Export MySQL Database & Users

Get list of databases on server


mysql -uroot -paliali12 -e "show databases"
Dump/export all Databases
mysqldump -uroot -paliali12 --all-databases > databases.sql

Get list of MySQL databases users on server


mysql -B -N -uroot -paliali12 -e "SELECT user, host FROM user" mysql

Save my mySQL users as .txt file on /root folder


mysql -B -N -uroot -p -e "SELECT CONCAT('\'', user,'\'@\'', host, '\'') FROM user WHERE
user != 'debian-sys-maint' AND user != 'root' AND user != ''" mysql > mysql_all_users.txt
Give root sql password.

Now Convert DATABASE Users from .txt file as we have created


above,and save it to as .sql file
while read line; do mysql -B -N -uroot -paliali12 -e "SHOW GRANTS FOR $line"; done <
mysql_all_users.txt > mysql_all_users_sql.sql

Add semicolon on end of every user command(; its semicolon symbol)


on .sql file with below commands
sed -i 's/$/;/' mysql_all_users_sql.sql

OK!! we have backup database and mysql database users on server

Import MySQL Database & users


Get list of DATABASE Name from old server before remove or reinstall
new OS on VPS.
mysql -uroot -paliali12 -e "show databases"
Note this Database names on a text file or anywhere

For create multiple databases with single command on new server as


you have on old server
mysql -uroot -paliali12 -e "CREATE DATABASE paktuts_cloud;CREATE DATABASE
paktuts_news;CREATE DATABASE paktuts_paktv;CREATE DATABASE
paktuts_soft;CREATE DATABASE paktuts_tuts;"

For import the MySQL Databases Users


mysql -u root -p < mysql_all_users_sql.sql
Import databases dumps to new server
mysql -uroot -paliali12 < databases.sql

Import single database from dump databases backup by using below


command.
mysql --one-database paktuts_tuts < databases.sql

That's it !!!!

You might also like