主从复制的基本概念:
1.主从复制的条件:
实时灾备,用于故障切换读写分离,提供查询服务备份,避免影响业务
2.主从复制原理:
从库生成两个线程,一个i/o线程,一个SQL线程;i/o线程去请求主库的binlog,并且得到的binlog日志写道relay log(中继日志)文件中,主库会生成一个log dump线程,用来给从库的i/o线程传binlog;SQL线程,会读取中继日志文件,并解析成具体的操作执行,这样主从的操作就一致了,而最终的数据也就一直了。
3.主从复制存在的问题以及解决办法:
主库宕机之后,数据可能会丢失从库只有一个sql Thread,主库写压力大,复制很可能延时
解决方法:
半同步复制解决–解决数据丢失的问题并行复制–解决从库复制延时的问题
【server1】 master IP:172.25.48.1【server2】 slave IP:172.25.48.2
注: mysql 数据库的版本,两个数据库版本要相同,或者 slave 比 master 版本高!
一.配置安装数据库:
【server1】主库配置
1.下载安装包:
mysql-community-client.x86_64 0:5.7.17-1.el6
mysql-community-common.x86_64 0:5.7.17-1.el6
mysql-community-libs.x86_64 0:5.7.17-1.el6
mysql-community-libs-compat.x86_64 0:5.7.17-1.el6
mysql-community-server.x86_64 0:5.7.17-1.el6
2.配置文件,添加数据库ID:
[root@server1 ~]# vim /etc/my.cnf
server-id=1
log-bin=mysql-bin
[root@server1 ~]# /etc/init.d/mysqld start
Initializing MySQL database: [ OK ]
Installing validate password plugin: [ OK ]
Starting mysqld: [ OK ]
3.初始化数据库:
[root@server1 ~]# grep password /var/log/mysqld.log
2018-08-08T08:41:49.496479Z 1 [Note] A temporary password is generated for root@localhost: 1pVt3VzrL,IB
[root@server1 ~]# mysql_secure_installation
Securing the MySQL server deployment.
Enter password for user root:
The existing password for the user account root has expired. Please set a new password.
New password:
Re-enter new password:
The 'validate_password' plugin is installed on the server.
The subsequent steps will run with the existing configuration
of the plugin.
Using existing password for root.
Estimated strength of the password: 100
Change the password for root ? ((Press y|Y for Yes, any other key for No) :
... skipping.
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.
Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
Success.
Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.
Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y
Success.
By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.
Remove test database and access to it? (Press y|Y for Yes, any other key for No) :
... skipping.
Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.
Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success.
All done!
4.登陆数据库:
[root@server1 ~]# mysql -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 15
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>
5.给登陆用户可复制权限:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql> grant replication slave on *.* to repl@'172.25.39.%' identified by 'Xa85215295##';
Query OK, 0 rows affected, 1 warning (0.06 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 843 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
6.【server2】用repl测试登陆
[root@server2 ~]# mysql -u repl -p -h 172.25.39.1
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 16
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>
【server2】从库配置
1.配置文件,添加数据库ID:
从服务器 ID 号,不要和主 ID 相同,如果设置多个从服务器,每个从服务器必须有一个唯一的 server-id 值,必须与主服务器的以及其它从服务器的不相同。可以认为 server-id 值类似于 IP 地址:这些 ID 值能唯一识别复制服务器群集中的每个服务器实例。
[root@server2 ~]# vim /etc/my.cnf
server-id=2
[root@server2 ~]# /etc/init.d/mysqld start
Starting mysqld: [ OK ]
2.初始化数据库:
[root@server2 ~]# grep password /var/log/mysqld.log
2018-08-08T09:03:10.015626Z 1 [Note] A temporary password is generated for root@localhost: ya3W5pslkk,f
[root@server2 ~]# mysql_secure_installation
Securing the MySQL server deployment.
Enter password for user root:
The existing password for the user account root has expired. Please set a new password.
New password:
Re-enter new password:
The 'validate_password' plugin is installed on the server.
The subsequent steps will run with the existing configuration
of the plugin.
Using existing password for root.
Estimated strength of the password: 100
Change the password for root ? ((Press y|Y for Yes, any other key for No) :
... skipping.
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.
Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
Success.
Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.
Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y
Success.
By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.
Remove test database and access to it? (Press y|Y for Yes, any other key for No) :
... skipping.
Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.
Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success.
All done!
2.在【server2】中添加与主库连接,查看与主库是否联通:
mysql> change master to master_host='172.25.39.1',master_user='repl',master_password='Xa85215295##',master_log_file='mysql-bin.000003',master_log_pos=843;
Query OK, 0 rows affected, 2 warnings (0.71 sec)
mysql> start slave;
Query OK, 0 rows affected (0.02 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.25.39.1
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 843
Relay_Log_File: server2-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000003
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: 843
Relay_Log_Space: 529
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: 1
Master_UUID: e4071ad2-9ae6-11e8-9c2a-525400ed3db7
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
注意:
· 当看到Slave_IO_Running: Yes以及Slave_SQL_Running: Yes,则表示slave库已经正常运行了
· 当出现Slave_IO_Running: Connecting的提示时,说明主库和从库没有连接上,有以下三点原因:
· 1.网络问题:检查网络连接是否能够连接上
· 2.密码或POS号错误:查看pos号和主库的号是否对应
· 3.防火墙的问题:查看主库防火墙的策略,数据库是否拒绝外来连接,然后做相应的改动
二.添加主库数据进行测试:
1.【server1】主库添加数据:
mysql> create database test;
Query OK, 1 row affected (0.41 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
5 rows in set (0.00 sec)
mysql> use test
Database changed
mysql> create table userlist(
-> username varchar(15) not null,
-> password varchar(25) not null);
Query OK, 0 rows affected (0.49 sec)
mysql> insert into userlist values ('user1','123');
Query OK, 1 row affected (0.14 sec)
mysql> insert into userlist values ('user2','344');
Query OK, 1 row affected (0.16 sec)
mysql> insert into userlist values ('user3','789');
Query OK, 1 row affected (0.11 sec)
2.【server2】从库查看数据:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
5 rows in set (0.00 sec)
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> select * from userlist;
+----------+----------+
| username | password |
+----------+----------+
| user1 | 123 |
| user2 | 344 |
| user3 | 789 |
+----------+----------+
3 rows in set (0.00 sec)
GTID主从复制
1.在【server1】【server2】中配置:
[root@server1 ~]# vim /etc/my.cnf
gtid_mode=ON
enforce-gtid-consistency=true
[root@server1 ~]# /etc/init.d/mysqld restart
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]
2.开启【server2】从库,查看连接:
[root@server2 ~]# mysql -p
Enter password:
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> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.25.39.1
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 154
Relay_Log_File: server2-relay-bin.000005
Relay_Log_Pos: 367
Relay_Master_Log_File: mysql-bin.000004
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: 154
Relay_Log_Space: 742
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: 1
Master_UUID: e4071ad2-9ae6-11e8-9c2a-525400ed3db7
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
mysql> stop slave;
Query OK, 0 rows affected (0.03 sec)
mysql> change master to master_host='172.25.39.1',master_user='repl',master_password='Xa85215295##',MASTER_AUTO_POSITION=1;
Query OK, 0 rows affected, 2 warnings (0.35 sec)
mysql> start slave;
Query OK, 0 rows affected (0.07 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.25.39.1
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 154
Relay_Log_File: server2-relay-bin.000002
Relay_Log_Pos: 367
Relay_Master_Log_File: mysql-bin.000004
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: 154
Relay_Log_Space: 576
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: 1
Master_UUID: e4071ad2-9ae6-11e8-9c2a-525400ed3db7
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: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
ERROR:
No query specified
3.在【server1】中删除user3数据,并在【server2】查看:
【server1】
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 |
+----------------+
| userlist |
+----------------+
1 row in set (0.00 sec)
mysql> select * from userlist;
+----------+----------+
| username | password |
+----------+----------+
| user1 | 123 |
| user2 | 344 |
| user3 | 789 |
+----------+----------+
3 rows in set (0.00 sec)
mysql> delete from userlist where username='user3';
Query OK, 1 row affected (0.13 sec)
mysql> select * from userlist;
+----------+----------+
| username | password |
+----------+----------+
| user1 | 123 |
| user2 | 344 |
+----------+----------+
2 rows in set (0.00 sec)
【server2】
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> select * from userlist;
+----------+----------+
| username | password |
+----------+----------+
| user1 | 123 |
| user2 | 344 |
+----------+----------+
2 rows in set (0.00 sec)
建立多个从库:
首先先搭建【server5】主库,【server6】从库的GTID主从复制
给三台虚拟机都安装数据库包
一.搭建第二个【server7】从库:
(以【server6】为主库,这样可以减少【server5】数据库的负载)
1.配置【server6】:vim /etc/my.cnf
添加:
log-bin=mysql-bin
log-slave-updates
2.重启数据库:
登陆数据库,查看连接状态,保证【server6】与【server5】没有断开连接
[root@server6 ~]# /etc/init.d/mysqld restart
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]
3.配置【server7】:vim /etc/my.cnf
重启数据库
添加:
server-id=7
gtid_mode=ON
enforce-gtid-consistency=true
4.登陆【server7】数据库,进行初始化并修改密码
5.在【server6】中添加‘172.25.39.%’网段用户的复制查看权限:
mysql> grant replication slave on *.* to repl@'172.25.39.%' identified by 'Xa85215295##';
Query OK, 0 rows affected, 1 warning (0.05 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+--------------------------------------------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+--------------------------------------------------------------------------------+
| mysql-bin.000001 | 447 | | | 7b4cea55-9c4d-11e8-8955-525400f9cbe2:1,
b6801fd0-9c4a-11e8-b227-525400b9273c:1 |
+------------------+----------+--------------+------------------+--------------------------------------------------------------------------------+
1 row in set (0.00 sec)
6.先将【server6】中的数据发送到【server7】中,使数据同步,再进行下一步,不然首先数据都不同步,没法进行后续操作
[root@server6 ~]# mysqldump -p test > test.sql
Enter password:
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.
[root@server6 ~]# ls
anaconda-ks.cfg install.log install.log.syslog mysql test.sql
[root@server6 ~]# scp test.sql root@172.25.39.7:/root
The authenticity of host '172.25.39.7 (172.25.39.7)' can't be established.
RSA key fingerprint is ce:b7:35:21:60:9f:f3:8d:f4:25:af:73:ad:ad:bc:ab.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '172.25.39.7' (RSA) to the list of known hosts.
root@172.25.39.7's password:
test.sql 100% 2177 2.1KB/s 00:00
7.在【server7】中查看test.sql脚本,并在脚本中添加配置建立数据库
[root@server7 ~]# vim test.sql
8.把刚才传过来的数据在【server7】中导入到数据库中,并进行数据查看
[root@server7 ~]# mysql -p < test.sql
Enter password:
[root@server7 ~]# mysql -pXa85215295##
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
5 rows in set (0.00 sec)
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> select * from userlist;
+----------+----------+
| username | password |
+----------+----------+
| user1 | 123 |
| user2 | 123 |
+----------+----------+
2 rows in set (0.00 sec) ##数据相同,初步环境同步成功
9.在【server7】从库中添加和【server6】主库的连接,有两个yes表示连接成功
mysql> change master to master_host='172.25.39.6',master_user='repl',master_password='Xa85215295##',master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.55 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.6
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 447
Relay_Log_File: server7-relay-bin.000002
Relay_Log_Pos: 414
Relay_Master_Log_File: mysql-bin.000001
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: 447
Relay_Log_Space: 623
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: 6
Master_UUID: b6801fd0-9c4a-11e8-b227-525400b9273c
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: 7b4cea55-9c4d-11e8-8955-525400f9cbe2:1,
b6801fd0-9c4a-11e8-b227-525400b9273c:1
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
ERROR:
No query specified
10.测试连接:
(1)在【server5】主库中添加新的数据:
[root@server5 ~]# mysql -pXa85215295##
mysql> use test
mysql> insert into test.userlist values ('user3','333');
Query OK, 1 row affected (0.08 sec)
mysql> select * from userlist;
+----------+----------+
| username | password |
+----------+----------+
| user1 | 123 |
| user2 | 123 |
| user3 | 333 |
+----------+----------+
3 rows in set (0.00 sec)
(2)在【server7】中查看刚才在【server5】中添加的数据:
[root@server7 ~]# mysql -pXa85215295##
mysql> use test
mysql> select * from userlist;
+----------+----------+
| username | password |
+----------+----------+
| user1 | 123 |
| user2 | 123 |
| user3 | 333 |
+----------+----------+
3 rows in set (0.00 sec)
并行复制
【server6】 登陆mysql数据库:
配置文件,添加并行复制:
[root@server6 ~]# vim /etc/my.cnf
[root@server6 ~]# /etc/init.d/mysqld restart
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]
添加:
【server6】 再次登陆mysql数据库:没有延时
Mysql半同步
介于异步复制和全同步复制之间,主库在执行完客户端提交的事务后不是立刻返回给客户端,而是等待至少一个从库接收到并写到relay log中才返回给客户端。相对于异步复制,半同步复制提高了数据的安全性,同时它也造成了一定程度的延迟,这个延迟最少是一个TCP/IP往返的时间。所以,半同步复制最好在低延时的网络中使用。
1.【server5】主库加载mater模块:
mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';
Query OK, 0 rows affected (0.12 sec) ##安装插件
mysql> set global rpl_semi_sync_master_enabled=ON;
Query OK, 0 rows affected (0.00 sec) ##启动半同步复制
mysql> show variables like '%semi%'; #环境变量
+-------------------------------------------+------------+
| Variable_name | Value |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled | ON |
| rpl_semi_sync_master_timeout | 10000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_for_slave_count | 1 |
| rpl_semi_sync_master_wait_no_slave | ON |
| rpl_semi_sync_master_wait_point | AFTER_SYNC |
+-------------------------------------------+------------+
6 rows in set (0.04 sec)
mysql> show status like '%rpl_semi_sync%'; ##状态变量
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 0 |
| Rpl_semi_sync_master_net_avg_wait_time | 0 |
| Rpl_semi_sync_master_net_wait_time | 0 |
| Rpl_semi_sync_master_net_waits | 0 |
| Rpl_semi_sync_master_no_times | 0 |
| Rpl_semi_sync_master_no_tx | 0 |
| Rpl_semi_sync_master_status | ON |##半同步master模块开启
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 0 |
| Rpl_semi_sync_master_tx_wait_time | 0 |
| Rpl_semi_sync_master_tx_waits | 0 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 0 |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)
2.【server6】既是主库,也是从库,所以模块都需要加载:
[root@server6 ~]# mysql -pXa85215295##
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.25.39.5
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 464
Relay_Log_File: server6-relay-bin.000007
Relay_Log_Pos: 414
Relay_Master_Log_File: mysql-bin.000004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
mysql> stop slave;
Query OK, 0 rows affected (0.14 sec)
mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
Query OK, 0 rows affected (0.08 sec)
#安装slave插件
mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';
Query OK, 0 rows affected (0.06 sec)
#安装master插件
mysql> set global rpl_semi_sync_master_enabled=ON;
Query OK, 0 rows affected (0.00 sec)
#启动半同步复制
mysql> set global rpl_semi_sync_slave_enabled=ON;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like '%semi%'; #环境变量
+-------------------------------------------+------------+
| Variable_name | Value |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled | ON |
| rpl_semi_sync_master_timeout | 10000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_for_slave_count | 1 |
| rpl_semi_sync_master_wait_no_slave | ON |
| rpl_semi_sync_master_wait_point | AFTER_SYNC |
| rpl_semi_sync_slave_enabled | ON |
| rpl_semi_sync_slave_trace_level | 32 |
+-------------------------------------------+------------+
8 rows in set (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected (0.59 sec)
mysql> stop slave io_thread;
Query OK, 0 rows affected (0.08 sec)
mysql> start slave io_thread;
Query OK, 0 rows affected (0.00 sec)
# 重启从上的IO线程,如果没有重启,则默认还是异步复制,重启后,slave会在master上注册为半同步复制的slave角色。
mysql> show status like '%rpl_semi_sync%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 0 |
| Rpl_semi_sync_master_net_avg_wait_time | 0 |
| Rpl_semi_sync_master_net_wait_time | 0 |
| Rpl_semi_sync_master_net_waits | 0 |
| Rpl_semi_sync_master_no_times | 0 |
| Rpl_semi_sync_master_no_tx | 0 |
| Rpl_semi_sync_master_status | ON |##半同步master模块开启
| Rpl_semi_sync_master_timefunc_failures | 0 | ( 主库 )
| Rpl_semi_sync_master_tx_avg_wait_time | 0 |
| Rpl_semi_sync_master_tx_wait_time | 0 |
| Rpl_semi_sync_master_tx_waits | 0 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 0 |
| Rpl_semi_sync_slave_status | ON |##半同步master模块开启
+--------------------------------------------+-------+ ( 从库 )
15 rows in set (0.00 sec)
3.【server7】从库加载mater模块安装插件:
mysql> stop slave;
Query OK, 0 rows affected (0.05 sec)
mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
Query OK, 0 rows affected (0.05 sec)
mysql> set global rpl_semi_sync_slave_enabled=ON;
Query OK, 0 rows affected (0.00 sec) #启动半同步复制,也可写到配置文件中
mysql> start slave;
Query OK, 0 rows affected (0.03 sec)
mysql> select plugin_name,plugin_status
-> from information_schema.plugins
-> where plugin_name like '%semi%';
+---------------------+---------------+
| plugin_name | plugin_status |
+---------------------+---------------+
| rpl_semi_sync_slave | ACTIVE |
+---------------------+---------------+
1 row in set (0.00 sec)
4.进行测试:
(1)首先在【server5】master上查看以下参数,半同步是否正常开启:
mysql> show status like '%rpl_semi_sync%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 1 |
| Rpl_semi_sync_master_net_avg_wait_time | 0 |
| Rpl_semi_sync_master_net_wait_time | 0 |
| Rpl_semi_sync_master_net_waits | 0 |
| Rpl_semi_sync_master_no_times | 0 |
| Rpl_semi_sync_master_no_tx | 0 |
| Rpl_semi_sync_master_status | ON | ##开启半同步
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 0 |
| Rpl_semi_sync_master_tx_wait_time | 0 |
| Rpl_semi_sync_master_tx_waits | 0 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 0 |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)
(2)然后【server6】slave上关闭io_thread线程:
mysql> stop slave io_thread;
Query OK, 0 rows affected (0.04 sec)
(3)在【server5】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 test.userlist values ('user5','3333');
Query OK, 1 row affected (10.13 sec)
mysql> insert into test.userlist values ('user6','4444');
Query OK, 1 row affected (0.11 sec)
mysql> select * from userlist;
+----------+----------+
| username | password |
+----------+----------+
| user1 | 123 |
| user2 | 123 |
| user3 | 333 |
| user5 | 3333 |
| user6 | 4444 |
+----------+----------+
5 rows in set (0.00 sec)
(4)开启io_thread线程,再去查看数据库数据:
【server6】
mysql> start slave io_thread;
Query OK, 0 rows affected (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
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> select * from userliat;
ERROR 1146 (42S02): Table 'test.userliat' doesn't exist
mysql> select * from userlist;
+----------+----------+
| username | password |
+----------+----------+
| user1 | 123 |
| user2 | 123 |
| user3 | 333 |
| user5 | 3333 |
| user6 | 4444 | ##数据更新过来了
+----------+----------+
5 rows in set (0.00 sec)
mysql> show status like '%rpl_semi_sync%'; ##状态变量
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 1 |
| Rpl_semi_sync_master_net_avg_wait_time | 0 |
| Rpl_semi_sync_master_net_wait_time | 0 |
| Rpl_semi_sync_master_net_waits | 2 |
| Rpl_semi_sync_master_no_times | 0 |
| Rpl_semi_sync_master_no_tx | 0 |
| Rpl_semi_sync_master_status | ON |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 429 |
| Rpl_semi_sync_master_tx_wait_time | 858 |
| Rpl_semi_sync_master_tx_waits | 2 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 2 |##开启两个半同步端口
| Rpl_semi_sync_slave_status | ON |
+--------------------------------------------+-------+
15 rows in set (0.01 sec)
【server7】
mysql> use test;
Database changed
mysql> select * from userlist;
+----------+----------+
| username | password |
+----------+----------+
| user1 | 123 |
| user2 | 123 |
| user3 | 333 |
| user5 | 3333 |
| user6 | 4444 | ##数据也同步过来了
+----------+----------+
5 rows in set (0.00 sec)