Recently I needed to move 19 databases from server A to sever B. The new server B was a scaled down Linux server with no web services so I couldn't use phpMyAdmin, the MySQL database administration tool. I needed to learn how to take care of this task using the command line only.
After moving 19 databases, I created a fairly easy system for exporting, transferring, importing, and creating permissions.
Step 1: Dump Database
Use SSH and log into server A. Navigate to a temporary folder or create a new folder for dumping the databases into. From the command line issue the following command
-
mysqldump DATABASE_NAME > DATABASE_NAME.sql
This will dump the structure and data from the database you named above into the directory you are currently in.
Step 2: Transfer File
Now you need to transfer the file to the new server.
-
scp DATABASE_NAME.sql USERNAME@IP_ADDRESS:DIRECTORY_ON_SERVER_B/DATABASE_NAME.sql
Replace DATABASE_NAME with the name you gave the file in step 1. Replace USERNAME with the login name on server B. I used root. Replace IP_ADDRESS with the IP of server B. DIRECTORY_ON_SERVER_B is the name of the directory on server B where you want to transfer the file to. It doesn't really matter where you put it.
Once you issue the above command, you will be asked to provide the password for the username you supplied. Once accepted, the file will be transferred.
Now log into server B and navigate to the directory where you transferred the database file to.
Step 3: Create Database
-
mysql -u root -p -e 'CREATE DATABASE DATABASE_NAME';
This command will create a new database with the name you provide. Should be the same name as you used on server A. After entering the above command provide the MySQL root password and the new database will be created.
Step 4: Restore Data
-
mysql -u root -p DATABASE_NAME < DATABASE_NAME.sql;
This command will import the structure and data from the database on server A. Again you will be asked for the MySQL root password.
Step 5: Grant Users
-
grant all on DATABASE_NAME.* to DATABASE_USERNAME@localhost identified by 'PASSWORD';
Now you need to create a new user and give that user permission to access the database.
Step 6: Flush Privileges
-
FLUSH PRIVILEGES;
Now tell MySQL to reload the new privileges you created.
That's it. Repeat for each database you are moving.
Here are a few other commands that came in handy
Show the databases:
-
SHOW DATABASES;
Show Grants:
-
select User,Host from mysql.user;
Delete Grants:
-
DELETE FROM mysql.user WHERE User='DATABASE_USER' and host='localhost';
Welcome to johnhesch.com, a blog about my life, projects, and interests. I have been blogging for over 10 years in one form or another.
just bookmarked!
thanks!