migrating MySQL on Uhost to UDB

A description of the application scenario

Recently, we deployed a cloud host in Ucloud. Because of the underlying hardware failure, Ucloud recommends that the cloud host be migrated dynamically, but because the MySQL database is deployed on this server, the volume of data is too large and the cloud host migration can be slow. So we need to migrate this cloud host's MySQL to UDB first, then switch the MySQL configuration inside the program.

Two specific operation steps

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 amount of data is too large, the export time will be very long, you can use screen to open a window, you can view progress at any time

-A export all databases

--ignore-table=<database>.<table> does not export the specified table, if there are multiple tables that do not need to be exported, you need to use multiple--ignore-table parameters

--MASTER-DATA=[1|2] If you set--master-data=1, the following information will be displayed:

----Position to start replication or point-in-time recovery from--change MASTER to master_log_file= ' mysql-bin.000003 ', MA ster_log_pos=3018292;--

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 sync needs to follow the prompts here to set Master_log_file and Master_log_pos

Set the--master-data parameter to fit the--single-transaction parameter without locking the table.

--single-transaction guarantees the consistency of backup data and currently supports INNODB

--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 amount of data is too large, the import time will be very long, you can use screen to open a window, you can view progress at any time

Mysql-h10.4.21.160-uroot-p

Mysql> Source Mysql_data20150203.sql

There are several glitches when importing data here, because the UDB default is set to Master, the Binlog is turned on, so when importing data, UDB Binlog grows too fast, and when the 500G disk is full, the import fails.

You can set the import without writing Binlog

SET sql_log_bin=0

Since the use of the mysqldump export when using the-a parameter, does not filter out the MySQL library, so if the uhost MySQL is not set up in addition to the root account has the appropriate permissions to log in the account, then the following settings slave error.

In this case, you can restart UDB with the--skip-grant-tables parameter and then reset the root account password.

3. Set UDB to MySQL Slave, sync data from MySQL on Uhost

Change Master to master_host= ' 10.4.3.149 ', master_port=3306,master_user= ' repl_user ', master_password= ' Xyzzy ', master _log_file= ' mysql-bin.000003 ', master_log_pos=3018292;

During the synchronization process, look at the status information of the slave, mainly observe the following several parameters

Slave_io_running:yes

Slave_sql_running:yes

seconds_behind_master:0

In the process of synchronization, seconds_behind_master values will be smaller, if the synchronization error, there will be an error message display

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: 0master_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: 11 row in set  (0.00  SEC) mysql>

This article is from the Linux SA John blog, so be sure to keep this source http://john88wang.blog.51cto.com/2165294/1612777

migrating MySQL on Uhost to UDB