MySQL dual-Machine hot backup

System: CentOS Release 6.6 (Final)

Mysql:mysql Ver 14.14 Distrib 5.1.73, for Redhat-linux-gnu (x86_64) using ReadLine 5.1

Master-Slave dual-machine hot backup, <=> slave, the data will be synchronized to the other side. Suppose a has a database Test,ip address of 192.168.0.110, now to set up B with a two-machine hot backup, the IP address is 192.168.1.120.

1. Precautions

A. The MySQL version of the host cannot be higher than the MySQL version of the slave, which can be the same.

B. The port number used by the host and slave MySQL must be the same.

2. Open the 3306 port of the AB firewall:

sudo 3306 -J ACCEPTsudo service iptables Save

3. Set up MySQL replication users:

Server A

' Dbrep ' @'192.168.0.110';

Server B

' Dbrep ' @'192.168.0.120';

4. Configure MySQL

Server A:

sudovim/etc/my.cnf# added to [mysqld] section [Mysqld]#...server-ID=1Log-bin=mysql-log-Binbinlog_format=Mixedbinlog-ignore-db=Mysqlbinlog-ignore-db=Information_schemaReplicate- Do-db=Testrelay-log=mysql-relay-Loglog-slave-updates=trueRead-only=0Auto-increment-increment=2Auto-increment-offset=1

Server B:

sudovim/etc/my.cnf# added to [mysqld] section [Mysqld]#...server-ID=2Log-bin=mysql-log-Binbinlog_format=Mixedbinlog-ignore-db=Mysqlbinlog-ignore-db=Information_schemaReplicate- Do-db=Testrelay-log=mysql-relay-Loglog-slave-updates=trueRead-only=0Auto-increment-increment=2Auto-increment-offset=2

Comments:

The first group, each MySQL server sets different server-id to differentiate, Log-bin specifies the name of the binary log, and the saved format is mixed.

The second group ignores binary logs that do not save Mysql\information_schema.

The third group, the database that executes the change.

The fourth group, which prevents AB from having a conflict when writing data, sets the step of ID auto-increment to 2, with offsets of 1 and 2, respectively. There are only two servers, and if you have more than one, you can increase the stepping and set the respective offsets respectively.

The above parameters are described in the MySQL manual replication configuration section.

Restart the mysqld for the configuration to take effect.

5. Copy the data from A to B, keeping the same data as the initial state.

Server A

Mysql> flush tables with read lock;
Mysqldump-uroot-p iksdb > Dbtest.sql
Mysql-uroot-p

Note: multiple databases, mysqldump-uroot-p--databases test test2 test3 > Dbtest123.sql. All databases, mysqldump-uroot-p--all-database > Dball.sql. Table for a database, mysqldump-uroot-p db1 db1_tb1 db1_tb2 > Db1_tb1_tb2.sql

Server B

Mysql-uroot-p Test < Dbtest.sql

6. Create A to B copy

Server A MySQL Shell

Mysql> Show master status; +----------------------+----------+--------------+--------------------------+| File                 | Position | binlog_do_db | binlog_ignore_db         |+----------------------+----------+--------------+--------------------------+| Mysql-log-bin. 000003 |  2526733 |              | Mysql,information_schema |+----------------------+----------+--------------+--------------------------+1   in Set (0.00  sec) MySQL> Unlock tables;

Note: During backup of the database to show Master status, the lock state of the database is maintained to prevent data changes, resulting in replication errors due to initial state inconsistencies.

Server B MySQL Shell

Mysql>Change Master tomaster_host='192.168.0.110',    Master_user='Dbrep',    master_password='123456',    master_log_file='mysql-log-bin.000003',    master_log_pos=2526733; MySQL>slave start; Query OK,0Rows Affected (0.00sec) MySQL>Show Master Status\g***************************1. Row ***************************File:mysql-log-bin.000001Position:518Binlog_do_db:binlog_ignore_db:mysql,information_schema1RowinchSet (0.00sec) MySQL>Show Slave Status\g***************************1. Row ***************************slave_io_state:waiting forMaster to send event Master_host:192.168.0.110master_user:dbrep Master_port:3306Connect_retry: -Master_log_file:mysql-log-bin.000003Read_master_log_pos:2526733Relay_log_file:mysql-relay-log.000210Relay_log_pos:255Relay_master_log_file:mysql-log-bin.000003slave_io_running:yes Slave_sql_running:yes replicate_do_db:test repli Cate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_W Ild_ignore_table:last_errno:0Last_error:skip_counter:0Exec_master_log_pos:2526733Relay_log_space:559until_condition:none Until_log_file:until_log_pos:0Master_ssl_allowed:no Master_SSL_CA_File:Master_SSL_CA_Path:Master_SS L_Cert:Master_SSL_Cipher:Master_SSL_Key:Seconds_Behind_Master:0master_ssl_verify_server_cert:no Last_io_errno:0Last_io_error:last_sql_errno:0Last_sql_error:1RowinchSet (0.00sec) MySQL>

Note that slave_io_running:yes and Slave_sql_running:yes indicate a successful replication. Otherwise, there may be an inconsistency in the initial state of the connection, user, or database.

7, establish a copy of B to a, complete two-way backup

Server B MySQL Shell

Mysql> Show master status; +----------------------+----------+--------------+--------------------------+| File                 | Position | binlog_do_db | binlog_ignore_db         |+----------------------+----------+--------------+--------------------------+| Mysql-log-bin. 000001 |      518 |              | Mysql,information_schema |+----------------------+----------+--------------+--------------------------+1   in Set (0.00 sec)

Server A MySQL Shell

Mysql>slave stop; Query OK,0Rows Affected (0.03sec) MySQL>Change Master tomaster_host='192.168.0.120',    Master_user='Dbrep',    master_password='123456',    master_log_file='mysql-log-bin.000001',    master_log_pos=518; MySQL>slave start; Query OK,0Rows Affected (0.00sec) MySQL>Show Slave Status\g***************************1. Row ***************************slave_io_state:waiting forMaster to send event Master_host:192.168.0.120master_user:dbrep Master_port:3306Connect_retry: -Master_log_file:mysql-log-bin.000001Read_master_log_pos:518Relay_log_file:mysql-relay-log.000226Relay_log_pos:255Relay_master_log_file:mysql-log-bin.000001slave_io_running:yes Slave_sql_running:yes replicate_do_db:test repli Cate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_W Ild_ignore_table:last_errno:0Last_error:skip_counter:0Exec_master_log_pos:518Relay_log_space:559until_condition:none Until_log_file:until_log_pos:0Master_ssl_allowed:no Master_SSL_CA_File:Master_SSL_CA_Path:Master_SS L_Cert:Master_SSL_Cipher:Master_SSL_Key:Seconds_Behind_Master:0master_ssl_verify_server_cert:no Last_io_errno:0Last_io_error:last_sql_errno:0Last_sql_error:1RowinchSet (0.00sec) MySQL>

Note: The above data in B will not be manipulated in the environment, and if necessary, the database must be locked

Also see Slave_io_running:yes and Slave_sql_running:yes, indicating successful replication.

Note: In database operations, if there is no operation to select data, such as user test, This operation will not be written to the binary log , so the replication will not complete. Like "INSERT into test.test values", so that although it can be written to the master database, there will be no change in slave.

Reference:

Learn a little MySQL dual-machine hot standby----fast understanding MySQL Master-Slave, main master backup principle and practice

MySQL backup and recovery hot standby

MySQL 5.5 Reference Manual/replication

MySQL dual-Machine hot backup