MySQL5.7.10 Multi-copy function building

MySQL5.7.10 Multi-copy function building

1. Environment: centos6.5
[Email protected] mysql-5.7.10]# cat/etc/redhat-release
CentOS Release 6.5 (Final)
Host:

mysql-m1   192.168.56.21mysql -m2  192.168.56.22mysql-s    192.168.56.23

Now that the MYSQL-M1 database has been set up, I use my own virtual machine to clone and omit to install the database multiple times.
2. Ensure that the server_id Log-bin of the two main libraries are turned on and the server_id of the three libraries are different:

[[email protected] aliyun]# sed -n ‘36,37p‘ /etc/my.cnflog-bin = /aliyun/data/mysqllogs/bin-log/mysql-binserver_id = 1[[email protected] mysql-5.7.10]# sed -n ‘36,37p‘ /etc/my.cnflog-bin = /aliyun/data/mysqllogs/bin-log/mysql-binserver_id = 2[[email protected] mysql-5.7.10]# sed -n ‘36,37p‘ /etc/my.cnflog-bin = /aliyun/data/mysqllogs/bin-log/mysql-binserver_id = 3

Create test libraries and tables on 3.master:
3.1 Login Database mysql-m1 Create library test01 and table H1:

[Email protected] tmp]# mysql-uroot-p-s/tmp/mysql.sock

mysql> CREATE DATABASE test01; Query OK, 1 row affected (0.01 sec) mysql> use test01;database changedmysql> CREATE TABLE ' H1 ' (' id ' int (one) not N ULL auto_increment, ' B1 ' int (one) default null, ' B2 ' int (one) default null, ' B3 ' int (one) GENERATED always as (' B1 ' + ' B2 ')) VIRTUAL, PRIMARY KEY (' id ')) engine=innodb DEFAULT Charset=utf8 Collate=utf8_bin; Query OK, 0 rows affected (0.03 sec) mysql> desc h1;+-------+---------+------+-----+---------+-------------------+| Field | Type | Null | Key | Default | Extra |+-------+---------+------+-----+---------+-------------------+| ID | Int (11) | NO | PRI | NULL | auto_increment | | B1 | Int (11) |     YES | |                   NULL | || B2 | Int (11) |     YES | |                   NULL | || B3 | Int (11) |     YES | | NULL | VIRTUAL GENERATED |+-------+---------+------+-----+---------+-------------------+4 rows in Set (0.00 sec) mysql> INSERT into H1 (B1,B2) values (2,2); Query OK, 1 roW Affected (0.00 sec) mysql> SELECT * FROM h1;+----+------+------+------+| ID | B1 | B2 |  B3 |+----+------+------+------+|    1 |    2 |    2 | 4 |+----+------+------+------+1 row in Set (0.00 sec)

3.2 Landing Database mysql-m2 creating libraries test01 and tables and H2:

mysql> create database test01;Query OK, 1 row affected (0.03 sec)mysql>  CREATE TABLE `h2` (   `id` int(11) NOT NULL AUTO_INCREMENT,   `e1` int(11) DEFAULT NULL,   `e2` int(11) DEFAULT NULL,   `e3` int(11) GENERATED ALWAYS AS ((`e1` + `e2`)) VIRTUAL,   PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;Query OK, 0 rows affected (0.02 sec)mysql> insert into h2(e1,e2) values(3,3);Query OK, 1 row affected (0.06 sec)mysql> select * from h2;+----+------+------+------+| id | e1   | e2   | e3   |+----+------+------+------+|  1 |    3 |    3 |    6 |+----+------+------+------+1 row in set (0.00 sec)

The following non-stop lock table, automatic cutting Bin-log log, the full amount of backup Bin-log, import from library (this method is only suitable for the 5.5 version of the database backup, not suitable for 5.7 version of the database backup, to be verified) (not the steps of this document)

[[email protected] bin-log]# mysqldump -uroot -p -S /tmp/mysql.sock --master-data=2 -A -B -R -x -F --events|gzip >/tmp/m1.sql.gz[[email protected] mysql-5.7.10]#  mysqldump -uroot -p -S /tmp/mysql.sock --master-data=2 -A -B -R -x -F --events|gzip >/tmp/m2.sql.gz

Multi-sync database backup for 5.7 versions (different tables are synchronized to a database):

[[email protected] tmp]#  mysqldump -uroot -p123456 --master-data=2 --single-transaction --databases  --add-drop-database  test01  >m1.sql[[email protected] tmp]#  mysqldump -uroot -p123456 --master-data=2 --single-transaction --databases  --add-drop-database  test01  >m2.sql[[email protected] tmp]# cat m1.sql|grep "CHANGE MASTER"-- CHANGE MASTER TO MASTER_LOG_FILE=‘mysql-bin.000012‘, MASTER_LOG_POS=154;[[email protected] tmp]#  cat m2.sql|grep "CHANGE MASTER"-- CHANGE MASTER TO MASTER_LOG_FILE=‘mysql-bin.000010‘, MASTER_LOG_POS=154;[[email protected] tmp]#  scp -rp -P22 m1.sql 192.168.56.23:/tmp/[[email protected] tmp]#  scp -rp -P22 m2.sql 192.168.56.23:/tmp/

Operation on 4.slave:

[[email protected] tmp]# ll /tmp/total 396-rw-r--r--. 1 root  root  199233 May  8 01:15 m1.sql-rw-r--r--. 1 root  root  199243 May  8 01:14 m2.sqlsrwxrwxrwx. 1 mysql mysql      0 May  8 00:35 mysql.sock-rw-------. 1 mysql mysql      5 May  8 00:35 mysql.sock.lock

[[email protected] tmp]# mysql -uroot -p -S /tmp/mysql.sockmysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema || sys                || txt001             |+--------------------+5 rows in set (0.06 sec)

The data of master_1 and master_2 are imported into slave server on Slave respectively, and the way to change the MySQL storage master-info and relay-info from the library is changed from the file storage to table storage before importing. Add the following selections in MY.CNF:(note that this modification is from the library )

[[email protected] tmp]# cat -n /etc/my.cnf|sed -n ‘36,39p‘    36  log-bin = /aliyun/data/mysqllogs/bin-log/mysql-bin    37  server_id = 1    38  master_info_repository=TABLE    39  relay_log_info_repository=TABLE

Or again MySQL from the library command line operation:

[[email protected] ~]# mysql -uroot -p -S /tmp/mysql.sockmysql> stop slave;mysql> SET GLOBAL master_info_repository = ‘TABLE‘;Query OK, 0 rows affected (0.00 sec)mysql> SET GLOBAL relay_log_info_repository = ‘TABLE‘;Query OK, 0 rows affected (0.00 sec)

To import data into a database:
[Email protected] tmp]# mysql-uroot-p-s/tmp/mysql.sock </tmp/m2.sql
[Email protected] tmp]# mysql-uroot-p-s/tmp/mysql.sock </tmp/m1.sql

  [[email protected] ~]# mysql-uroot-p-s/tmp/mysql.sockmysql> show databases;+------------------- -+| Database |+--------------------+| Information_schema | | MySQL | | Performance_schema | | SYS | | test01 | | txt001 |+--------------------+6 rows in Set (0.00 sec) mysql> use test01;database changedmysql> show tabl es;+------------------+| tables_in_test01 |+------------------+| H1 | | H2 |+------------------+2 rows in Set (0.00 sec) mysql> SELECT * FROM h1;+----+------+------+------+| ID | B1 | B2 |  B3 |+----+------+------+------+|    1 |    2 |    2 | 4 |+----+------+------+------+1 row in Set (0.00 sec) mysql> SELECT * FROM h2;+----+------+------+------+| ID | E1 | E2 |  E3 |+----+------+------+------+|    1 |    3 |    3 | 6 |+----+------+------+------+1 row in Set (0.00 sec)  

5. Create a copy account on MYSQL-M1 and mysql-m2:
This operation is the same as the previous version of MySQL 5.7:
Create on MYSQL-M1

mysql> grant replication slave on *.* to ‘rep‘@‘192.168.56.23‘ identified by ‘123456‘;mysql> flush privileges;（必须刷新权限否则不生效的）

Create on MYSQL-M2

mysql> grant replication slave on *.* to ‘rep‘@‘192.168.56.23‘ identified by ‘123456‘;Query OK, 0 rows affected, 1 warning (0.02 secmysql> flush privileges; （必须刷新权限否则不生效的）

6. Change master on slave from the vault:

[[email protected] ~]# mysql -uroot -p -S /tmp/mysql.sockmysql> change master to MASTER_HOST=‘192.168.56.21‘ ,MASTER_USER=‘rep‘ ,MASTER_PASSWORD=‘123456‘ ,MASTER_LOG_FILE=‘mysql-bin.000012‘ ,MASTER_LOG_POS=154 FOR CHANNEL ‘mysql-m1‘;Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql>  change master to MASTER_HOST=‘192.168.56.22‘ ,MASTER_USER=‘rep‘ ,MASTER_PASSWORD=‘123456‘ ,MASTER_LOG_FILE=‘mysql-bin.000010‘ ,MASTER_LOG_POS=154 FOR CHANNEL ‘mysql-m2‘;Query OK, 0 rows affected, 2 warnings (0.03 sec)

mysql> start slave for CHANNEL ‘mysql-m1‘;Query OK, 0 rows affected (0.02 sec)mysql> start slave for CHANNEL ‘mysql-m2‘;Query OK, 0 rows affected (0.00 sec)mysql>

[[email protected] ~]# mysql-uroot-p123456-s/tmp/mysql.sock-e "SHOW SLAVE STATUS for CHANNEL ' mysql-m1 ' \g" |egrep "Sla Ve_io_running| Slave_sql_running "
MySQL: [Warning] Using a password on the command line interface can is insecure.
Slave_io_running:connecting (Error) Here is Yes is normal
Slave_sql_running:yes
Slave_sql_running_state:slave have read all relay log; Waiting for more updates
7. Synchronous Error Resolution Method:

7.1. Look at the error log:
[Email protected] ~]# Cat/aliyun/data/mysql/mysql_3306.err

7.2. Check if the firewall is off:
(firewall locally from the library and local to the main library)

[[email protected] ~]# mysql-urep-p123456-h192.168.56.21mysql: [Warning] Using a password on the command line inte Rface can be insecure.  ERROR 2003 (HY000): Can ' t connect to MySQL server on ' 192.168.56.21 ' (113) [[email protected] ~]# mysql-urep-p123456 -h192.168.56.22mysql: [Warning] Using a password on the command line interface can be insecure.  ERROR 2003 (HY000): Can ' t connect to MySQL server on ' 192.168.56.22 ' (113) [[email protected] ~]#/etc/init.d/iptables                         Stopiptables:setting chains to Policy accept:filter [OK]iptables:flushing Firewall rules: [OK]iptables:unloading modules: [[email protected] tmp]#/etc/init.d/iptables stopiptables:setting C Hains to policy accept:filter [OK]iptables:flushing firewall rules: [OK]iptable s:unloading modules: [[email protected] tmp]#/etc/init.d/iptables stopiptables:setting chains to policy accept:f Ilter [OK]iptables:flushing firewall rules: [OK]iptables:unloading modules: [ OK][[email protected] tmp]#

7.3. Modify/ECT/MY.CNF in server_id not valid:

[[email protected] ~]# mysql-urep-p123456-h192.168.56.21 successfully connected but from the library connection mysql-m1 or error: But from the Library connection mysql-m2 or error: [[email  protected] ~]# mysql-uroot-p123456-s/tmp/mysql.sock-e "SHOW SLAVE STATUS for CHANNEL ' mysql-m1 ' \g" |egrep "Slav E_io_running| Slave_sql_running|             Last_io_error "MySQL: [Warning] Using a password on the command line interface can be insecure. Slave_io_running:no slave_sql_running:yes last_io_error:fatal error:the Slave I/O thread sto PS because master and slave have equal MySQL server IDs; These IDs must be different for replication to work (or the--replicate-same-server-id option must is used on slave but th is does do sense;      Please check the manual before using it). Slave_sql_running_state:slave have read all relay log; Waiting for more updates last_io_error_timestamp:160508 03:03:05 because server_id is modified in the/ECT/MY.CNF configuration file. The modification did not take effect without restarting the service [[email protected] mysql-5.7.10]# Cd/aliyun/server/mysql-5.7.10/pkilL mysqld[[email protected] mysql-5.7.10]# sh start_mysql.sh three databases all in one operation 

7.4. But still the error:

 [[email protected] ~]# mysql -uroot -p123456 -S /tmp/mysql.sock -e "SHOW SLAVE STATUS FOR CHANNEL ‘mysql-m1‘\G"|egrep  "Slave_IO_Running|Slave_SQL_Running|Last_IO_Error"mysql: [Warning] Using a password on the command line interface can be insecure.             Slave_IO_Running: No            Slave_SQL_Running: Yes                Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates      Last_IO_Error_Timestamp: 160508 03:23:36

The reason is that the mysql-s virtual machine is based on mysql-m1 virtual machine cloning, mysqlm1 on the file auto.cnf and mysql-s/mysql=m2 file auto.cnf is the same, so error

[[email protected] mysql]# cat /aliyun/data/mysql/auto.cnf [auto]server-uuid=3652e06e-1467-11e6-ab9f-000c2970db66[[email protected] mysql-5.7.10]# cat /aliyun/data/mysql/auto.cnf [auto]server-uuid=3652e06e-1467-11e6-ab9f-000c2970db66[[email protected] mysql-5.7.10]# cat /aliyun/data/mysql/auto.cnf [auto]server-uuid=3652e06e-1467-11e6-ab9f-000c2970db66把这三个auto.cnf文件中的数值改成随便不一样的就行。然后依次重启数据库，就好了

[[email protected] mysql-5.7.10]# mysql -uroot -p123456 -S /tmp/mysql.sock -e "SHOW SLAVE STATUS FOR CHANNEL ‘mysql-m1‘\G"|egrep  "Slave_IO_Running|Slave_SQL_Running|Last_IO_Error"mysql: [Warning] Using a password on the command line interface can be insecure.             Slave_IO_Running: Yes            Slave_SQL_Running: Yes                Last_IO_Error:       Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates      Last_IO_Error_Timestamp:

The

can view the synchronization status by viewing the Performance_schema related tables, executing the command:

Mysql> SELECT * from performance_schema.replication_connection_status, monitoring replication status +--------------+------------+------ --------------------------------+-----------+---------------+---------------------------+---------------------- ----+--------------------------+-------------------+--------------------+----------------------+| Channel_name | Group_name | Source_uuid | thread_id | Service_state | Count_received_heartbeats | Last_heartbeat_timestamp | Received_transaction_set | Last_error_number | Last_error_message | Last_error_timestamp |+--------------+------------+--------------------------------------+-----------+--------- ------+---------------------------+--------------------------+--------------------------+-------------------+-- ------------------+----------------------+|            mysql-m1 | |      3652e06e-1467-11e1-ab9f-000c2970db66 | NULL |                        OFF | 37 |                          2016-05-08 04:26:38 |                 |0 | | 0000-00-00 00:00:00 | |            mysql-m2 | |        3652e06e-1467-11e6-ab9f-000c2970db66 | 41 |                        On | 47 |                          2016-05-08 04:51:32 |                 |                    0 | | 0000-00-00 00:00:00 |+--------------+------------+--------------------------------------+-----------+----------- ----+---------------------------+--------------------------+--------------------------+-------------------+---- ----------------+----------------------+

mysql> stop Slave; (you must turn on each slave at the same time, otherwise you cannot sync )

Query OK, 0 rows affected (0.01 sec)mysql> set global sql_slave_skip_counter=10;mysql> start slave for CHANNEL ‘mysql-m1‘;mysql> start slave for CHANNEL ‘mysql-m2‘;mysql> SHOW SLAVE STATUS FOR CHANNEL ‘mysql-m1‘\Gmysql> SHOW SLAVE STATUS FOR CHANNEL ‘mysql-m2‘\G

The use of multi-source replication for 8.MySQL 5.7:
8.1, MySQL 5.7 Multi-source replication, can effectively solve the data statistics of the sub-database table, but also can be implemented in a slave server to multiple primary server data backup.

8.2, MySQL 5.7 of the emergence of multi-source replication, we do not need to use MARIADB Multi-master one from the architecture, so many small partners see New Hope.
Resources:

Http://dev.mysql.com/doc/refman/5.7/en/change-master-to.html
Http://www.longlong.asia/2015/10/21/mysql57-new-features.html

MySQL-5.7 document to see the test information:
Http://www.cnblogs.com/xuanzhi201111/p/5148113.html
MySQL Multiple copy data address
Http://www.mamicode.com/info-detail-1189601.html
MySQL Database 5.7 compilation installation
Http://www.cnblogs.com/xuanzhi201111/p/5148113.html
Http://www.mamicode.com/info-detail-1189601.html
http://huqiji.iteye.com/blog/2068613

MySQL5.7.10 Multi-copy function building