Steps to migrate MySQL or MariaDB database with MySQLDump utility

Leave a reply

Comprehensive SQL Backup Process: How to Backup and Restore Databases

Note: If the load is same only migrating database, then need to check old database parameter files like connection, session, memory, CPU etc

  1. Find the user which need to migrate with the database.

Associate user with the database and check all the privileges assigned to user by Show user

SELECT User, Host, db FROM mysql.user;
SELECT User, Host, authentication_string FROM mysql.user;

Generate the user creation with password:

SELECT Concat('ALTER USER ',user,'@'host' IDENTIFIED BY PASSWORD ''', authentication_string, '''') from mysql.user;

Example:
ALTER USER user1@localhost IDENTIFIED BY PASSWORD '*54958E764CE10E50764C2EECBB71D01F08549980';

Check the privileges of users and make a script file from following commands.

SHOW GRANTS FOR <username>@<host>;

2. Check the count of objects from the database.

select 'Procedure' as object_type,count(*) as Noofcount
from DatabaseName.information_schema.routines 
where routine_type in ('PROCEDURE') and routine_schema='dbname'
UNION
select 'Function',count(*) from DatabaseName.information_schema.routines where routine_type in ('FUNCTION') and routine_schema = 'dbname'
UNION
SELECT 'Tables',COUNT(*) FROM information_schema.tables WHERE table_schema = 'dbName';

3. Start the backup process with SQL DUMP

Backup the specified database:
mysqldump -u root -p --opt [database name] > [database name].sql

-- Backup all the databases present in server
mysqldump -u root -p --all-databases > all_databases.sql

4. Copy paste the SQL/backup file to new server.

5. Create the users present in old server by using first step.

6. Restore the database by connecting to the server using mysql utility.

mysql -u root -p newdatabase < newdatabase.sql

-- For all database
mysql -h remoteserver -u root -p < all_databases.sql

7. Verify the count of objects.

Leave a Reply