MySQL database service migration through the master-slave replication mechanism _ MySQL

The 0x00 background service data center is abolished, and the original service machine is out of warranty. the MySQL master-slave replication mechanism is used to seamlessly migrate the MySQL data service. 0x01 preparation 1. environment: in principle, it is best to set up mysql master-slave replication in the operating system version and environment. the MySQL version and configuration are consistent, with 0x00 background.

The data center where the business is located is withdrawn, and the original business machine has been out of warranty. the MySQL master-slave replication mechanism is used to seamlessly migrate the MySQL data service.

0x01 preparation

1. environment:

In principle, it is best to set up mysql master-slave replication in the operating system version, environment, MySQL version, and configuration to ensure the stability of the MySQL master-slave cluster and reduce exceptions caused by versions and environments, it is easy to troubleshoot and locate problems.

Since the machines involved in migration often went online a long time ago, and there was never a mechanism for upgrading related systems and services, it is better that the MySQL version involved this time is relatively high, the compatibility with MySQL 5.6 is still relatively good. thank you, thank you.

Original machine environment:

IP: A (the machine has been recycled)

System version: suse 11 linux x64

Mysql version: mysql 5.5.3

Configuration file path: None

Program Startup method:/bin/sh/usr/local/mysql/bin/mysqld_safe &

New machine environment:

IP: xxxxxx B

System version: tlinux 1.2 64bit (centos 6.2)

Mysql version: mysql-5.6.25

Configuration file path:/etc/my. cnf

Program Startup method:/etc/init. d/mysqld start

2. installation package preparation

Download the source code package:

Cmake: yum install cmake (2.6.4) or download the https://cmake.org/files/v3.3/cmake-3.3.2.tar.gz

MySQL: http://dev.mysql.com/downloads/mysql/5.6.html#downloads

0x02 MySQL installation and configuration

1. Installation (this is not the focus of this article. If an error occurs, you need to analyze the problem)

In principle, it is recommended to compile and install the operating environment by using the source code to fully utilize the features of the current machine. However, since we have compiled and installed the relevant deployment packages in the same system environment, therefore, the actual installation process is skipped. The installation process here is a general installation process:

A. install yum

Yum install mysql-server (recommended for tlinux2.0, corresponding to centos 7.0, fedora 20 +)

Unfortunately, the version of the tilnux 1.2 environment yum installed for the mysql-5.1.61 is outdated.

B. Compile and install the source code

Mysql-5.6.25groupadd mysql cd

Useradd-g mysql-s/bin/false

Mkdir-p/data/dbdata

Chown mysql: mysql/data/dbdata

Cmake.-DCMAKE_INSTALL_PREFIX =/usr/local/mysql-DMYSQL_DATADIR =/data/dbdata-DSYSCONFDIR =/etc/

Make & make install

Cp/usr/local/mysql/support-files/my-default.cnf/etc/my. cnf

Cd/usr/local/

Mv mysql mysql-5.6.25 & ln-s mysql-5.6.25 mysql

Cp/usr/local/mysql/support-files/mysql. server/etc/init. d/mysqld

Chmod + x/etc/init. d/mysqld

Chkconfig -- add mysqld

Chkconfig mysqld on

Initialize mysql:

/Usr/local/mysql/scripts/mysql_install_db -- user = mysql -- basedir =/usr/local/mysql -- datadir =/data/dbdata

Vi/etc/profile

Add: export PATH = $ PATH:/usr/local/mysql/bin

/Etc/init. d/mysqld start

2. configuration

Old Machine:

Log on to the mysql service terminal:

Set the server id:

Set gloabl server_id = 2; select @ server_id;

Enable binlog:

SET SQL _LOG_BIN = 1;

Set global binlog_format = 'mixed'; # table structure changes are recorded in statement mode. statements that modify data such as update or delete are used to record changes to all rows.

Mysql> select @ server_id;

+ ------------- +

| @ Server_id |

+ ------------- +

| 2 |

+ ------------- +

1 row in set (0.00 sec)

New machine:

Vi/etc/my. cnf

Log-bin =/data/dbdata/binlog/mysql-binbinlog_format = MIXED

Binlog_cache_size = 4 M

Max_binlog_cache_size = 1024 M

Max_binlog_size = 1024 M

Expire_logs_days = 5log-slave-updates

Servers-id = 20151109

Mysql> select @ server_id; + ------------- +

| @ Server_id | + ------------- +

| 1, 20151109 |

+ ------------- + 1 row in set (0.00 sec)

0x03 data export and import

1. Data export:

There are not many databases involved, and the database reads and writes are not frequent. we choose to use mysqldump to export mysql data.

Because you want to add master/slave information, you need to add-master-data = 1 with the lock table operation. when the storage engine of the table is InnoDB, the addition of -- single-transaction can reduce the impact of the lock table. to be precise, there will only be a short Global read lock, which is much better than the lock table of MyISAM.

Mysqldump-u root -- default-character-set = utf8-Y-B -- set-charset -- single-transaction -- master-data = 1 hehehhe>/data/backup/databases/hehehehhe20151109. SQL

CHANGE MASTER

-Y, -- all-tablespaces

Dump all the tablespaces.

-B, -- databases Dump several databases. Note the difference in usage; in

If you dump your databases at once (using the option

-- Databases = or -- all-databases), the logs will be

Locks all tables internal SS all databases. This is achieved

-- All-databases or -- databases is given.

-- Master-data [= #] This causes the binary log position and filename to be

Appended to the output. If equal to 1, will print it as

Change master command; if equal to 2, that command will

Be prefixed with a comment symbol. This option will turn

-- Lock-all-tables on, unless -- single-transaction is

Specified too (in which case a global read lock is only

Taken a short time at the beginning of the dump; don't

Forget to read about -- single-transaction below). In all

Cases, any action on logs will happen at the exact moment

Of the dump. Option automatically turns -- lock-tables

Off.

-- Dump-slave [= #] This causes the binary log position and filename of

Master to be appended to the dumped data output. Setting

The value to 1, will printit as a change master command

In the dumped data output; if equal to 2, that command

Will be prefixed with a comment symbol. This option will

Turn -- lock-all-tables on, unless -- single-transaction is

Specified too (in which case a global read lock is only

Taken a short time at the beginning of the dump-don't

Forget to read about -- single-transaction below). In all

Cases any action on logs will happen at the exact moment

Of the dump. Option automatically turns -- lock-tables off.

-- Include-master-host-port

Adds 'master _ HOST = , MASTER_PORT = 'To' CHANGE

Master to .. 'in dump produced with -- dump-slave.

-- Single-transaction

Creates a consistent snapshot by dumping all tables in

Single transaction. Works ONLY for tables stored in

Storage engines which support multiversioning (currently

Only InnoDB does); the dump is NOT guaranteed to be

Consistent for other storage engines. While

-- Single-transaction dump is in process, to ensure

Valid dump file (correct table contents and binary log

Position), no other connection shocould use the following

Statements: alter table, drop table, rename table,

Truncate table, as consistent snapshot is not isolated

From them. Option automatically turns off -- lock-tables.

-- Set-charset Add 'set NAMES default_character_set 'to the output.

(Defaults to on; use -- skip-set-charset to disable .)

View Master/slave information:

[Root @ WEBAPP_ B _IP_HOST/data/backup/databases] #

It comes with the command to switch between master and slave synchronization points. Note that when adding this command, you need to export all the database data in the master-slave synchronization status together.

Grep CHANGE/data/backup/databases/hehehehhe20151109. SQL

Change master to MASTER_LOG_FILE = 'MySQL-bin.000001 ', MASTER_LOG_POS = 120;

2. data import:

New machine:

Create database 'hehehhes '/*! 40100 default character set utf8 */;

After setting up master-slave synchronization, stop slave, directly import data through mysql, and then start slave.

0x04 master-slave replication configuration and data synchronization

1. MySQL binlog

Binlog is the basis for MySQL master-slave replication. MySQL uses binlog to record changes to database data. it can be used to build a master-slave replication cluster, or use mysqlbinlog to recover some data exceptions through binlog.

In the event of a disaster, you should use the last full backup to restore the database, and then use the backup log file to restore the database to the nearest available state. Logs must be restored in sequence, that is, the earliest log files must be restored first.

Common binlog Operation Commands

1. view the list of all binlog logs

Mysql> show master logs; (binlog information when the new machine acts as the master)

[Root @ WEBAPP_ B _IP_HOST/data/dbdata/binlog] # pwd/data/dbdata/binlog

[Root @ WEBAPP_ B _IP_HOST/data/dbdata/binlog] # lltotal 882136-rw-rw ---- 1 mysql admin 27317 Nov 9 mysql-bin.000001-rw-rw ---- 1 mysql admin 1034478 Nov 9 mysql-bin.000002-rw-rw ---- 1 mysql admin 531 Nov 9 mysql-bin.000003-rw-rw ---- 1 mysql admin 903407219 Nov 12 mysql-bin.000004-rw-rw ---- 1 mysql admin 148 Nov 9 mysql-bin.index

[Root @ WEBAPP_ B _IP_HOST/data/dbdata/binlog] # mysql-e "show master logs;" + ------------------ + ----------- +

| Log_name | File_size |

+ ------------------ + ----------- +

| Mysql-bin.000001 | 27317 |

| Mysql-bin.000002 | 1034478 |

| Mysql-bin.000003 | 531 |

| Mysql-bin.000004 | 903407219 |

+ ------------------ + ----------- +

2. check the master status, that is, the ID name of the last (latest) binlog log and its Position value for the last operation event.

Mysql> show master status; (the latest binlog location information of the master server when the new machine acts as the slave)

[Root @ WEBAPP_ B _IP_HOST/data/dbdata/binlog] # mysql-e "show master status;" + -------------------- + ----------- + -------------- + ------------------ + ------------------- +

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+ ------------------ + ----------- + -------------- + ------------------ + ------------------- +

| Mysql-bin.000004 | 903407219 |

+ ------------------ + ----------- + -------------- + ------------------ + ------------------- +

3. refresh the log to generate a new binlog log file from now on.

Mysql> flush logs;

Mysql> flush logs;

Query OK, 0 rows affected (0.00 sec)

Mysql> show master logs;

+ ------------------ + ----------- +

| Log_name | File_size |

+ ------------------ + ----------- +

| Mysql-bin.000001 | 27317 |

| Mysql-bin.000002 | 1034478 |

| Mysql-bin.000003 | 531 |

| Mysql-bin.000004 | 903407266 |

| Mysql-bin.000005 | 120 |

+ ------------------ + ----------- + 5 rows in set (0.00 sec)

Note: When the mysqld service is restarted, this command is automatically executed to refresh the binlog. when the-F option is added to the mysqldump backup data, the binlog is also refreshed;

4. Reset (clear) all binlog

Mysql> reset master;

Mysql> reset master;

Query OK, 0 rows affected (0.08 sec)

Mysql> show master logs;

+ ------------------ + ----------- +

| Log_name | File_size |

+ ------------------ + ----------- +

| Mysql-bin.000001 | 120 |

+ ------------------ + ----------- + 1 row in set (0.00 sec)

Clears the binlog of the current machine.

5. clean up

Clear binlog

PURGE {MASTER | BINARY} logs to 'log _ name' // log_name will not be cleared

PURGE {MASTER | BINARY} logs before 'date' // date is not cleared

2. master-slave replication configuration

1) create a master-slave synchronization account on the old machine (master A_IP:

Grant replication slave on *. * to 'rep '@' B _ IP' identified by 'heheh ';

2) view the binlog status of the current old machine (master A_IP)

Mysql> show master status; + ------------------ + ---------- + ---------------- + ------------------- + | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set

+ ------------------ + ---------- + -------------- + -------------------- + ------------------- +

| Mysql-bin.000001 | 120 |

+ ------------------ + ---------- + -------------- + -------------------- + ------------------- + 1 row in set (0.00 sec)

3) Create master-slave synchronization on the New Machine (B _IP)

Mysql> change master to master_host = 'a _ IP ',

Master_user = 'Rep ',

Master_password = 'heheheh ',

Master_port = 3306,

Master_log_file = 'MySQL-bin.000001 ',

Master_log_pos = 120,

Master_connect_retry = 10;

Parameters:

Master_host: IP address of the master server.

Master_user: username created when configuring the master server

Master_password: user password

Master_port: the mysql port of the master server. if it has not been modified, the default value is enough.

Master_log_file: log file name. fill in the Filemaster_log_pos: log location displayed when you view the master status. fill in the Positionmaster_connect_retry: Number of reconnections displayed when you view the master status.

4) start the process

Mysql> start slave;

View Master-slave synchronization:

Pay attention to Slave_IO_Running: YES; Slave_ SQL _Running: YES; Seconds_Behind_Master: 0

Mysql> show slave status \ G; ***************************** 1. row ***************************

Slave_IO_State:

Master_Host: A_IPMaster_User: rep

Master_Port: 3306Connect_Retry: 60Master_Log_File: Tencent64-bin.000164Read_Master_Log_Pos: drawing relay_log_file: WEBAPP_ B _IP_HOST-relay-bin.000006Relay_Log_Pos: 270Relay_Master_Log_File: Tencent64-bin.000164Slave_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: 0Last_Error:

Skip_Counter: 0Exec_Master_Log_Pos: unknown relay_log_space: 786Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0Master_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

Command for viewing master/slave information:

Show slave hosts \ G; view slave information on the host

Show master status \ G; view status information on the host

Show slave status \ G; View Master/slave status information from the machine

0x05 import of stored procedures and permissions

1. export stored procedures and functions

Because the stored procedure and database permission information are stored in the mysql database, common parameters through mysqldump are not exported.

Export the stored procedure: add the-R (or -- routines) parameter to mysqldump.

-R, -- routines Dump stored routines (functions and procedures ).

Add 'set @ GLOBAL. GTID_PURGED 'to the output. Possible @ GLOBAL. GTID_PURGED' is added to the output. If GTIDs

Statements: alter table, drop table, rename table,

Truncate table, as consistent snapshot is not isolated

Export the stored procedure and other content separately:

Mysqldump-uroot-n-d-t-R hehehhe> procedure_name. SQL

-- MySQL dump 10.10 --

-- Host: localhost Database: hehehehhe

--------------------------------------------------------

-- Server version 5.5.3-m3-log

Later, it was also found that if mysqldump specified -- databases during export, the original DB creation statement will be automatically added, but sometimes we want to specify the DB creation statement, for example, if you want to specify the default character set as utf8 and use latin1, you do not need to add this parameter.

2. export and import Database permissions

The database permissions are global and specific database and table permissions, and cannot be exported and then imported.

You can use the show grants statement to obtain the permissions of accounts and IP addresses authorized by old machines.

Mysql> select concat ("show grants for", user, "@", host, ";") from mysql. user;

+ ---------------------------------------------- +

| CONCAT ("show grants for", user, "@", host, ";") |

+ ---------------------------------------------- +

| Show grants for hehehehhe@10.136.12.216; | show grants for hehehehhe@10.166.129.173; | show grants for hehehehhe@10.166.129.174; | show grants for adbreader@10.166.129.226; | show grants for hehehehhe@10.166.129.226; | show grants for root@127.0.0.1; | show grants for root @: 1; | show grants for root@TENCENT64.site; | show grants for root @ localhost; | + -------------------------------------------- + 9 rows in set (0.00 sec)

Then, place the show grants statement in the script and run the script to obtain the relevant authorization information on the old machine.

Finally, you can import related permissions by running the authorization information script on the new machine.

Note: an error may occur here. if the database does not exist, you can import the relevant permissions. Therefore, you must filter the permissions before importing the database.

0x06 data verification and Business verification

Data verification and Business verification are not the focus, but are an indispensable part of database migration.

There are two ways to verify data:

1. check the data record and master-slave synchronization status, which can be easily and quickly determined, but is not the most accurate.

2. run the script to check the checksum value of each table in the database, which is accurate and may be slow.

3. use pt-table-checksum in Percona Toolkit for MySQL to perform master-slave verification.

0x07 troubleshooting

1. MyIsam storage engine lock table causes service interruption and affects services.

We previously communicated with our mutual entertainment brothers in RTX. during data backup, the MyISAM lock table took too long, leading to service exceptions. Therefore, we generally recommend setting up a dedicated backup machine to back up data, and try to use the INNODB storage engine.

2. during data verification, percona is used to discover data inconsistency. during data processing, the auto-increment ID is changed. the specified service data cannot be found through the auto-increment ID.

When data is inconsistent, pt-table-sync is used for restoration first. However, this solution is to insert data when there is a lack of data, replace data when there is a conflict, and the auto-increment ID changes.

We recommend that you do not use auto-increment IDs as business attributes.

3. account for pitfalls, not to be continued. You are welcome to continue writing them together.

0x08 others

Everything is possible. more practices in the lab environment will reduce the chance of time-and-space failures.

Fortunately, this migration of MySQL version is relatively high. if it encounters 5.0 or 5.1, it will kill.

Thanks to Percona, a second-level supervisor can also ensure the stability of mysql services.

Thank you for your country and your parents.

Thank you for your discussion.