Migrate MySQL on Uhost to UDB

Migrate MySQL on Uhost to UDB

I. Application Scenario Description
Recently, a cloud host deployed on Ucloud is recommended for dynamic migration due to underlying hardware faults. However, because the MySQL database is deployed on this server, the data volume is too large, migration of VM instances is slow. Therefore, we need to first migrate the MySQL of this VM instance to UDB, and then switch the MySQL configuration in the program.

2. Procedure
1. Export a copy of MySQL on the Uhost
Mysqldump-A -- ignore-table = mysql. slow_log -- master-data = 1 -- single-transaction -- quick-R -- event-uroot-p> mysql_data20150203. SQL

If the data volume is too large, the export time will be long. You can use screen to open a window and view the progress at any time.

-A: export all databases
-- Ignore-table = <database>. <table> the specified table is not exported. If multiple tables do not need to be exported, multiple -- ignore-table parameters must be used.

-- Master-data = [1 | 2] If -- master-data = 1 is set, the following information is displayed:

--
-- Position to start replication or point-in-time recovery from
--

Change master to MASTER_LOG_FILE = 'mysql-bin.000003 ', MASTER_LOG_POS = 3018292;

--

In this way, if you set SLAVE, you do not need to specify MASTER_LOG_FILE and MASTER_LOG_POS separately.
How TO Set -- master-data = 2, change master to this line will be commented out, when setting SLAVE synchronization needs TO follow the prompts here TO set MASTER_LOG_FILE and MASTER_LOG_POS

Set the -- master-data parameter, and use the -- single-transaction parameter to keep the table unlocked.

-- Single-transaction ensures consistency of backup data. Currently, InnoDB is supported.

-- Quick Don't buffer query, dump directly to stdout.
-R: export functions and stored procedures
-- Event Dump events

2. import data to UDB
If the data volume is too large, the import time will be long. You can use screen to open a window and check the progress at any time.

Mysql-h10.4.21.160-uroot-p
Mysql> source mysql_data20150203. SQL

There are several minor faults during data import. Since the binlog function is set to Master by default, the binlog function is enabled. Therefore, when the data is imported, the binlog function increases too fast. After the GB disk is fully written, import failed.

You can set not to write binlog during import.

SET SQL _log_bin = 0

Because the-A parameter is used for mysqldump export and the mysql database is not filtered out, if the MySQL of the Uhost does not have an account with proper logon permissions except the root account, an error occurs when setting Slave as follows.
In this case, you can use the -- skip-grant-tables parameter to restart UDB, and then reset the root account password.

3. Set UDB to MySQL Slave to synchronize data from MySQL on the Uhost.
Change master to MASTER_HOST = '10. 4.3.149 ', MASTER_PORT = 3306, MASTER_USER = 'repl _ user', MASTER_PASSWORD = 'xysy', MASTER_LOG_FILE = 'mysql-bin.000003', MASTER_LOG_POS = 3018292;

Check the Slave status information during synchronization. Observe the following parameters:
Slave_IO_Running: Yes
Slave_ SQL _Running: Yes
Seconds_Behind_Master: 0

During synchronization, the Seconds_Behind_Master value will be smaller and smaller. If a synchronization error occurs, an error message will be displayed.

Mysql> show slave status \ G
* *************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.4.3.149
Master_User: repl_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000005
Read_Master_Log_Pos: 925459151
Relay_Log_File: mysql-relay.000012
Relay_Log_Pos: 294731160
Relay_Master_Log_File: mysql-bin.000005
Slave_IO_Running: Yes
Slave_ SQL _Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 925459151
Relay_Log_Space: 294731312
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_ SQL _Errno: 0
Last_ SQL _Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
1 row in set (0.00 sec)

Mysql>

4. Stop MySQL on the Uhost.
5. Change the MySQL connection information in the program.
6. Cancel the Slave settings of UDB.
Use the stop slave command to STOP the SLAVE settings on UDB.
Mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

Mysql> show slave status \ G
* *************************** 1. row ***************************
Slave_IO_State:
Master_Host: 10.4.3.149
Master_User: repl_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000006
Read_Master_Log_Pos: 351201559
Relay_Log_File: mysql-relay.000016
Relay_Log_Pos: 15191656
Relay_Master_Log_File: mysql-bin.000006
Slave_IO_Running: No
Slave_ SQL _Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 351201559
Relay_Log_Space: 15191808
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 2003
Last_IO_Error: error reconnecting to master 'repl _ user@10.4.3.149: 100'-retry-time: 60 retries: 3306
Last_ SQL _Errno: 0
Last_ SQL _Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
1 row in set (0.00 sec)

Mysql>

12345 mysql> reset slave all;
Query OK, 0 rows affected (0.02 sec)

Mysql> show slave status \ G
Empty set (0.00 sec)

Then, use the reset slave all command to clearly understand the SLAVE settings on UDB.

This article permanently updates the link address: