mysql的主从复制、基于gtid的主从复制和半同步复制
程序员文章站
2022-05-08 23:08:14
...
主从复制
在官网上下载mysql的安装包mysql-5.7.17-1.el6.x86_64.rpm-bundle.tar
在server1，2中均做以下动作
tar -xf mysql-5.7.17-1.el6.x86_64.rpm-bundle.tar
lsyum install -y mysql-community-client-5.7.17-1.el6.x86_64.rpm
mysql-community-common-5.7.17-1.el6.x86_64.rpm
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
vim /etc/my.cnf
server-id=1 # server2（从节点）中只书写server-id，并且与server1中的不同
log-bin=mysql-bin
/etc/init.d/mysqld start # 开启mysql
grep password /var/log/mysqld.log # 过滤初始密码mysql_secure_installation # mysql的初始化
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: #输入新密码，密码含有大、小写字母、数字、特殊符号的组合，不低于8位
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 # 不允许root用户远程登陆
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) : # 删除test库
... 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! 主节点上：
mysql -p
mysql> grant replication slave on . to aaa@qq.com’172.25.11.%’ identified by ‘WDd12345.’;
Query OK, 0 rows affected, 1 warning (0.07 sec)
在从节点上测试是否可以登陆
主节点：
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 843 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)从节点：
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> change master to master_host='172.25.11.1',master_user='repl',master_password='WDd12345.',master_log_file='mysql-bin.000001',master_log_pos=843;
Query OK, 0 rows affected, 2 warnings (0.78 sec)
mysql> start slave;
Query OK, 0 rows affected (0.04 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.25.11.1
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 843
Relay_Log_File: server2-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table: Slave_IO_Running和Slave_SQL_Running为yes证明运行正常。
主节点上插入值
mysql> create database test;
Query OK, 1 row affected (0.12 sec)
mysql> use test;
Database changed
mysql> create table userlist(
-> username varchar(10) not null,
-> password varchar(20) not null);
Query OK, 0 rows affected (0.52 sec)
mysql> desc userlist;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(10) | NO | | NULL | |
| password | varchar(20) | NO | | NULL | |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
mysql> insert into userlist values('user1','111');
Query OK, 1 row affected (0.08 sec)
mysql> insert into userlist values('user2','222');
Query OK, 1 row affected (0.10 sec)
mysql> select * from userlist;
+----------+----------+
| username | password |
+----------+----------+
| user1 | 111 |
| user2 | 222 |
+----------+----------+
2 rows in set (0.00 sec)从结点上查看：
gtid的主从复制
主、从结点上：
vim /etc/my.cnf
gtid_mode=ON
enforce_gtid_consistency=true
/etc/init.d/mysqld restart在从节点上：
mysql> stop slave;
Query OK, 0 rows affected (0.07 sec)
mysql> change master to master_host='172.25.11.1',master_user='repl',master_password='WDd12345.',MASTER_AUTO_POSITION = 1;
Query OK, 0 rows affected, 2 warnings (0.23 sec)
mysql> start slave;
Query OK, 0 rows affected (0.05 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.25.11.1
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 154
Relay_Log_File: server2-relay-bin.000002
Relay_Log_Pos: 367
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB: 主节点：
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> delete from userlist where username='user1';
Query OK, 1 row affected (0.17 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 |
+----------+----------+
| user2 | 222 |
+----------+----------+
1 row in set (0.00 sec)半同步复制
主节点
mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';
Query OK, 0 rows affected (0.18 sec)
mysql> set global rpl_semi_sync_master_enabled=ON;
Query OK, 0 rows affected (0.00 sec)
mysql> show global 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.00 sec)从节点
mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
Query OK, 0 rows affected (0.06 sec)
mysql> set global rpl_semi_sync_slave_enabled=ON;
Query OK, 0 rows affected (0.00 sec)
mysql> stop slave io_thread;
Query OK, 0 rows affected (0.03 sec)
mysql> start slave io_thread;
Query OK, 0 rows affected (0.00 sec)
mysql> show global variables like '%semi%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled | ON |
| rpl_semi_sync_slave_trace_level | 32 |
+---------------------------------+-------+
2 rows in set (0.00 sec)主节点
插入一条值
mysql> insert into userlist value('user6','666');
Query OK, 1 row affected (0.11 sec)
mysql> show global status like '%semi%';
+--------------------------------------------+-------+
| 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 | 1 |
| 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 | 4375 |
| Rpl_semi_sync_master_tx_wait_time | 4375 |
| Rpl_semi_sync_master_tx_waits | 1 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 1 |# 成功次数
+--------------------------------------------+-------+关闭slave的半同步，插入数据，测试
从节点
mysql> set global rpl_semi_sync_slave_enabled=OFF;
Query OK, 0 rows affected (0.00 sec)
mysql> stop slave io_thread;
Query OK, 0 rows affected (0.05 sec)
mysql> start slave io_thread;
Query OK, 0 rows affected (0.00 sec)
mysql> show global variables like '%semi%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled | OFF |
| rpl_semi_sync_slave_trace_level | 32 |
+---------------------------------+-------+主节点
mysql> insert into userlist value('user7','777');
Query OK, 1 row affected (10.09 sec)
mysql> show global status like '%semi%';
+--------------------------------------------+-------+
| 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 | 1 |
| Rpl_semi_sync_master_no_times | 1 |
| Rpl_semi_sync_master_no_tx | 1 |
| Rpl_semi_sync_master_status | OFF |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 4375 |
| Rpl_semi_sync_master_tx_wait_time | 4375 |
| Rpl_semi_sync_master_tx_waits | 1 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 1 |
+--------------------------------------------+-------+