MySQL的主从复制（gtid）

Mysql

一般来说都是通过 主从复制（ Master-Slave ）的方式来同步数据，再通过读写分离（MySQL-Proxy）来提升数据库的并发负载能力。

主从复制：我的理解就是一台服务器充当主服务器 master，另外一或者多个服务器充当从服务器 slave。主服务器的数据同步到从服务器中。（是主服务器对数据库修改记录二进制日志，从服务器通过主服务器的二进制日志自动更新）

主服务器即 mysql-A 是 master，从服务器即 mysql-B 是 slave。

图片来自网络：

mysql 主从复制：

实验环境： rhel6.5 mysql5.1.71 master 172.25.78.3 slave 172.25.78.2 proxy 172.25.78.4

注意事项：

（1）两台服务器需时间同步(主从)； （2）相互都能 ping 通； （3）selinux 状态是关闭的，火墙关闭。

实验过程：

保证环境绝对纯净（rhel6.5）

[root@server2 ~]# rpm -qa | grep mysql mysql-5.1.71-1.el6.x86_64 mysql-server-5.1.71-1.el6.x86_64 mysql-libs-5.1.71-1.el6.x86_64 [root@server2 ~]# rpm -e mysql-5.1.71-1.el6.x86_64 mysql-server-5.1.71-1.el6.x86_64

1.get mysql-5.7.17-1.el6.x86_64.rpm-bundle.tar tar xf mysql-5.7.17-1.el6.x86_64.rpm-bundle.tar

2.安装

yum install -y mysql-community-libs-5.7.17-1.el6.x86_64.rpm mysql-community-libs-compat-5.7.17-1.el6.x86_64.rpm mysql-community-server-5.7.17-1.el6.x86_64.rpm mysql-community-client-5.7.17-1.el6.x86_64.rpm mysql-community-common-5.7.17-1.el6.x86_64.rpm

3.打开服务 [root@server2 ~]# /etc/init.d/mysqld start Initializing MySQL database: [ OK ] Installing validate password plugin: —发现密码更新 [ OK ] Starting mysqld: [ OK ] [root@server2 ~]# grep password /var/log/mysqld.log —查看新更新的密码

2017-08-03T07:20:25.288668Z 1 [Note] A temporary password is generated for root@localhost: CKv)R:ato1jo 2017-08-03T07:22:00.981658Z 0 [Note] Execution of init_file '/var/lib/mysql/install-validate-password-plugin.uxbuwk.sql' started. 2017-08-03T07:22:01.128358Z 0 [Note] Execution of init_file '/var/lib/mysql/install-validate-password-plugin.uxbuwk.sql' ended. 2017-08-03T07:22:03.588639Z 0 [Note] Shutting down plugin 'sha256_password' 2017-08-03T07:22:03.588676Z 0 [Note] Shutting down plugin 'mysql_native_password' 2017-08-03T07:22:09.533402Z 3 [Note] Access denied for user 'UNKNOWN_MYSQL_USER'@'localhost' (using password: NO) [root@server2 ~]# mysql -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 5 Server version: 5.7.17 Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> ALTER USER root@localhost identified by 'Westos+007';（给root重新设定密码） Query OK, 0 rows affected (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.00 sec) mysql>

4.配置/etc/my.cf文件 [root@server2 ~]# vim /etc/my.cnf 在[mysqld]下面添加

server-id=2 log-bin=mysql-bin binlog-do-db=test binlog-ignore-db=mysql

5.重启服务，配置mysql数据库 [root@server2 ~]# /etc/init.d/mysqld restart Stopping mysqld: [ OK ] Starting mysqld: [ OK ]

[root@server2 ~]# mysql -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.7.17-log MySQL Community Server (GPL) Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.00 sec) mysql> grant replication slave on *.* to mysql@'172.25.39.%' identified by 'Westos+007';-----授权（此时使用slave测试是否能正常登陆） Query OK, 0 rows affected, 1 warning (0.36 sec) mysql> create database test;---创建测试库 Query OK, 1 row affected (0.36 sec) mysql> use test; Database changed mysql> create table usertb ( username varchar(15) not null, -> password varchar(25) not null); Query OK, 0 rows affected (0.94 sec) mysql> desc usertb; +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | username | varchar(15) | NO | | NULL | | | password | varchar(25) | NO | | NULL | | +----------+-------------+------+-----+---------+-------+ 2 rows in set (0.02 sec) mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000001 | 829 | test | mysql | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) mysql> exit Bye

[root@server2 ~]# cd /var/lib/mysql [root@server2 mysql]# mysqlbinlog mysql-bin.000001—查看二进制文件

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #170803 15:54:42 server id 2 end_log_pos 123 CRC32 0x3aeee8c0 Start: binlog v 4, server v 5.7.17-log created 170803 15:54:42 at startup # Warning: this binlog is either in use or was not closed properly. ROLLBACK/*!*/; BINLOG ' wtaCWQ8CAAAAdwAAAHsAAAABAAQANS43LjE3LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAADC1oJZEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA AcDo7jo= '/*!*/; # at 123 #170803 15:54:42 server id 2 end_log_pos 154 CRC32 0x9cc8791c Previous-GTIDs # [empty] # at 154 #170803 15:56:32 server id 2 end_log_pos 219 CRC32 0x01cc7430 Anonymous_GTID last_committed=0 sequence_number=1 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 219 #170803 15:56:32 server id 2 end_log_pos 448 CRC32 0x633a5146 Query thread_id=4 exec_time=0 error_code=0 SET TIMESTAMP=1501746992/*!*/; SET @@session.pseudo_thread_id=4/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; SET @@session.sql_mode=1436549152/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C utf8 *//*!*/; SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; GRANT REPLICATION SLAVE ON *.* TO 'mysql'@'172.25.39.%' IDENTIFIED WITH 'mysql_native_password' AS '*613EED914F9F17B554041EC92F2CB6BFA4F64799' /*!*/; # at 448 #170803 15:57:37 server id 2 end_log_pos 513 CRC32 0xb223e524 Anonymous_GTID last_committed=1 sequence_number=2 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 513-----在此节点创建test库，为了环境一致，从此刻让slave服务器同步复制 #170803 15:57:37 server id 2 end_log_pos 607 CRC32 0xee39ea2d Query thread_id=4 exec_time=0 error_code=0 SET TIMESTAMP=1501747057/*!*/; create database test /*!*/; # at 607----pos号日志的标识 #170803 16:00:15 server id 2 end_log_pos 672 CRC32 0xbf4b4426 Anonymous_GTID last_committed=2 sequence_number=3 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 672 #170803 16:00:15 server id 2 end_log_pos 829 CRC32 0xdea3b399 Query thread_id=4 exec_time=1 error_code=0 use `test`/*!*/; SET TIMESTAMP=1501747215/*!*/; create table usertb ( username varchar(15) not null, password varchar(25) not null) /*!*/; SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/; DELIMITER ; # End of log file /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

在slave端： 1.安装mysql修改密码。

2.测试是否能正常登陆master服务器 mysql -umysql -p -h172.25.39.2—登陆之后及时退出

3.更改配置文件、重启服务 [root@server4 ~]# vim /etc/my.cnf

server-id=4---类似于ip，不能和其他服务器的冲突。（必须配置）

[root@server4 ~]# /etc/init.d/mysqld restart

4.登陆mysql数据库

[root@server4 ~]# mysql -pWestos+007 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.7.17 MySQL Community Server (GPL) Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> change master to master_host='172.25.39.2',master_user='mysql',master_password='Westos+007',master_log_file='mysql-bin.000001',master_log_pos=513;--指定复制的地方以及从哪同步 Query OK, 0 rows affected, 2 warnings (0.44 sec) mysql> start slave; Query OK, 0 rows affected (0.44 sec) mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 172.25.39.2 Master_User: mysql Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 829 Relay_Log_File: server4-relay-bin.000002 Relay_Log_Pos: 636 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes--如果是no，通常是selinux和火墙；用户授权的问题 Slave_SQL_Running: Yes--一般是因为主从数据复制不一致造成的（与io两个日志写入relay-log<中继日志>） 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: 829 Relay_Log_Space: 845 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---表示延迟率为0，不延迟（但不考虑io延迟） 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: 2 Master_UUID: 37fe4b3c-781c-11e7-b8f2-525400848d33 Master_Info_File: /var/lib/mysql/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec) ERROR: No query specified

测试： 在master端：

mysql> use test; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> insert into usertb values ('user','111'); Query OK, 1 row affected (0.07 sec)

在slave端：

mysql> use test; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | usertb | +----------------+ 1 row in set (0.00 sec) mysql> select * from usertb; +----------+----------+ | username | password | +----------+----------+ | user | 111 | +----------+----------+ 1 row in set (0.00 sec) mysql>

一主多从：gdit模式–全局传输标识符

之前的是： 多slave端因为性能不同，同步也不同 当master端down了，那么同步多的为主，但是需要人工切换。不能自动

gtid使用的： 主从复制的切换gtid是全局的,随日志文件一起记录，只看自己的下一个标识next是多少，不管自己目前复制的。eg：目前复制到50，那么看51，如果存在就执行52，依次。 主down了，那么就选最大的为主。

更改为gtid： [root@server2 ~]# vim /etc/my.cnf(打开gtid)

35 gtid_mode=ON 36 enforce-gtid-consistency=true

[root@server2 ~]# /etc/init.d/mysqld restart Stopping mysqld: [ OK ] Starting mysqld: [ OK ] [root@server2 ~]# mysql -pWestos+007

mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 6 Server version: 5.7.17-log MySQL Community Server (GPL) Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> use test; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> insert into usertb values ('mz','150'); Query OK, 1 row affected (0.10 sec) mysql> select * from test.usertb; +----------+----------+ | username | password | +----------+----------+ | user | 111 | | wq | 111 | | wq1 | wwq | | mz | 150 | +----------+----------+ 4 rows in set (0.00 sec) mysql> use mysql; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; +---------------------------+ | Tables_in_mysql | +---------------------------+ | columns_priv | | db | | engine_cost | | event | | func | | general_log | | gtid_executed | | help_category | | help_keyword | | help_relation | | help_topic | | innodb_index_stats | | innodb_table_stats | | ndb_binlog_index | | plugin | | proc | | procs_priv | | proxies_priv | | server_cost | | servers | | slave_master_info | | slave_relay_log_info | | slave_worker_info | | slow_log | | tables_priv | | time_zone | | time_zone_leap_second | | time_zone_name | | time_zone_transition | | time_zone_transition_type | | user | +---------------------------+ 31 rows in set (0.00 sec) mysql> select * from gtid_executed; +--------------------------------------+----------------+--------------+ | source_uuid | interval_start | interval_end | +--------------------------------------+----------------+--------------+ | 37fe4b3c-781c-11e7-b8f2-525400848d33 | 1 | 2 | +--------------------------------------+----------------+--------------+ 1 row in set (0.00 sec) mysql> [root@server4 mysql]# vim /etc/my.cnf 31 gtid_mode=ON 32 enforce-gtid-consistency=true

[root@server4 ~]# mysql -pWestos+007

mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 6 Server version: 5.7.17 MySQL Community Server (GPL) Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> stop slave; Query OK, 0 rows affected (0.07 sec) mysql> change master to master_host='172.25.39.2',master_user='mysql',master_password='Westos+007',MASTER_AUTO_POSITION=1; Query OK, 0 rows affected, 2 warnings (0.31 sec) mysql> start slave; Query OK, 0 rows affected (0.03 sec) mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 172.25.39.2 Master_User: mysql Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000006 Read_Master_Log_Pos: 459 Relay_Log_File: server4-relay-bin.000002 Relay_Log_Pos: 632 Relay_Master_Log_File: mysql-bin.000006 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: 459 Relay_Log_Space: 841 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: 2 Master_UUID: 37fe4b3c-781c-11e7-b8f2-525400848d33 Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: 37fe4b3c-781c-11e7-b8f2-525400848d33:3(gtid打开成功) Executed_Gtid_Set: 37fe4b3c-781c-11e7-b8f2-525400848d33:1-3----Gtid模式显示成功 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec) ERROR: No query specified mysql> exit Bye