server10和server11安装mysql:
yum 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
/etc/init.d/mysqld start
#初始化(方式一)
grep password /var/log/mysqld.log
mysql_secure_installtion
mysql -p
#初始化方式二:
/etc/init.d/mysqld start
mysql -p
mysql> ALTER USER root@localhost identified by 'Workhard@345';
mysql> flush privileges;
主从复制
master配置:
vim /etc/my.cnf
添加:
server-id=1
log-bin=mysql-bin
master授权:
mysql -p
mysql> grant replication slave on *.* to ly@'172.25.92.%' identified by 'Workhard@345';
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 597 | | | |
+------------------+----------+--------------+------------------+-------------------+
slave配置:
vim /etc/my.cnf
添加:
server-id=2
slave指定mster:
[root@server11 mysql]# mysql -uroot -p
mysql> CHANGE MASTER TO MASTER_HOST='172.25.92.10',MASTER_USER='ly',MASTER_PASSWORD='Workhard@345',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=597;
Query OK, 0 rows affected, 2 warnings (1.21 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> start slave;
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event #这个是指slave 连接到master的状态。
Master_Host: 172.25.92.10 #master主机
Master_User: ly #这个是master上面的一个用户。用来负责主从复制的用户 ,创建主从复制的时候建立
Master_Port: 3306
Connect_Retry: 60 # master-connect-retry选项的当前值
Master_Log_File: mysql-bin.000001 #I/O线程当前正在读取的主服务器二进制日志文件的名称。
Read_Master_Log_Pos: 597 #在当前的主服务器二进制日志中,I/O线程已经读取的位置。
Relay_Log_File: server11-relay-bin.000002 #slave的SQL线程当前正在读取和执行的中继日志文件的名称。
Relay_Log_Pos: 320 #在当前的中继日志中,slave的SQL线程已读取和执行的位置。
Relay_Master_Log_File: mysql-bin.000001 #由SQL线程执行的包含多数近期事件的主服务器二进制日志文件的名称。
Slave_IO_Running: Yes #I/O线程是否被启动并成功地连接到主服务器上。
Slave_SQL_Running: Yes #SQL线程是否被启动。
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: #slave的SQL线程读取日志参数的的错误数量和错误消息
Exec_Master_Log_Pos: 597 #来自主服务器的二进制日志的由SQL线程执行的上一个时间的位置
Relay_Log_Space: 530 #所有原有的中继日志结合起来的总大小。
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 #表示主从之间的时间差 是数字的时候表示相差多少秒 null表示未知数,一般主从复制出问题了会出现null的情况。
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: 24f8e881-0499-11e8-9bc0-52540039110b
Master_Info_File: /var/lib/mysql/master.info
Skip_Counter: 0
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)
测试:master插入数据,slave可以看见:
master上:
mysql> create database test;
mysql> create table userdb (
-> username varchar(15) not null,
-> password varchar(20) not null);
mysql> desc userdb;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(15) | NO | | NULL | |
| password | varchar(20) | NO | | NULL | |
+----------+-------------+------+-----+---------+-------+
mysql> insert into userdb values ('user1','111');
Query OK, 1 row affected (0.15 sec)
mysql> insert into userdb values ('user2','222');
Query OK, 1 row affected (0.37 sec)
slave上可以查看到。
数据备份
[root@server10 mysql]# mysqlbinlog -v mysql-bin.000001
[root@server10 mysql]# mysqlbinlog --start-datetime='2018-01-29 11:00:12' --stop-datetime='2018-01-29 13:56:41' mysql-bin.000001 > test.sql #按时间端备份并保存zai
[root@server10 mysql]# mysql -p test < test.sql #将备份数据倒入到test库中
基于GTID的复制
master和slave上:
vim /etc/my.cnf
添加:
gtid_mode=ON
enforce-gtid-consistency=true
slave上:
mysql> STOP SLAVE;
mysql> change master to master_host='172.25.92.10',master_user='ly',master_password='Workhard@345',master_auto_position=1;
/etc/init.d/mysql restart
测试:
master:
[root@server10 mysql]# mysql -uroot -p
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
slave:
mysql> show slave status\G;
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 1
master:
mysql> insert into userdb values('usera','aaa');
mysql> show master status;
+------------------+----------+--------------+------------------+----------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+----------------------------------------+
| mysql-bin.000004 | 422 | | | 24f8e881-0499-11e8-9bc0-52540039110b:1 |
+------------------+----------+--------------+------------------+----------------------------------------+
slave上:
mysql> show slave status\G;
Retrieved_Gtid_Set: 24f8e881-0499-11e8-9bc0-52540039110b:1
Executed_Gtid_Set:
Auto_Position: 1
MysqlProxy实现读写分离
server6上:
server6上:
wget https://downloads.mysql.com/archives/get/file/mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz
tar zxvf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz
mv mysql-proxy-0.8.5-linux-el6-x86-64bit /usr/local/mysql-proxy
vim /etc/mysql-proxy.cnf
[mysql-proxy]
#user=root
#plugins=proxy
proxy-address=172.25.92.6
proxy-read-only-backend-addresses=172.25.92.5:3306
proxy-backend-addresses=172.25.92.4:3306
proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua
log-file=/var/log/mysql-proxy.log
log-level=debug
daemon=true
#keepalive=true
plugins=admin
admin-username='admin'
admin-password='admin'
admin-lua-script=/usr/local/mysql-proxy/lib/mysql-proxy/lua/admin.lua
vim /usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua
38 if not proxy.global.config.rwsplit then
39 proxy.global.config.rwsplit = {
40 min_idle_connections = 1,
41 max_idle_connections = 2,
42
43 is_debug = false
44 }
ll /etc/mysql-proxy.cnf
chmod 660 /etc/mysql-proxy.cnf
cd /usr/local/mysql-proxy/bin/
./mysql-proxy --defaults-file=/etc/mysql-proxy.cnf --plugins=proxy --plugins=admin #启动mysql-proxy和admin
[root@server6 bin]# netstat -antlp #可以看到3306和4041端口
Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 890/sshd
tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN 966/master
tcp 0 0 172.25.92.6:4041 0.0.0.0:* LISTEN 1144/mysql-proxy
tcp 0 0 172.25.92.6:3306 0.0.0.0:* LISTEN 1144/mysql-proxy
server10上:主
mysql> grant select,update,insert on *.* to proxy@'172.25.92.%' identified by 'Workhard@345'; #授权远程用户proxy可以执行select,update,insert操作
mysql> flush privileges;
mysql> use mysql;
mysql> select * from user; #两台主机上都有proxy用户。
令外开启三个tables,一个是主机(172.25.254.92)另外两个是server6:
一个server6安装数据包监控:
[root@server6 ~]# yum install -y tcpdump
[root@server6 ~]# tcpdump -i eth0 port 3306
可以看到经过server6的数据包走向
一个server6执行admin查看状态:
mysql -uadmin -padmin -h 172.25.92.6 -P 4041 #登陆管理端
mysql> select * from backends;
+-------------+-------------------+-------+------+------+-------------------+
| backend_ndx | address | state | type | uuid | connected_clients |
+-------------+-------------------+-------+------+------+-------------------+
| 1 | 172.25.92.10:3306 | up | rw | NULL | 0 |
| 2 | 172.25.92.11:3306 | up | ro | NULL | 0 |
+-------------+-------------------+-------+------+------+-------------------+
这里的server6可以用来查看后端读写分离的主机状态
最后个server6和本机用来充当客户端,执行读写操作:
[root@foundation92 Desktop]# mysql -h 172.25.92.6 -uproxy -pWorkhard@345
MySQL [(none)]> show databases;
MySQL [test]> select * from userdb;
+----------+----------+
| username | password |
+----------+----------+
| user1 | 111 |
| user2 | 222 |
| user4 | 444 |
+----------+----------+
3 rows in set (0.00 sec)
MySQL [test]> insert into userdb values ('user5','555');
#当使用插入命令的使用,在admin的管理端可以看到server10开启(up)了
在第三台server6上:
mysql> use test;
mysql> select * from userdb;
+----------+----------+
| username | password |
+----------+----------+
| user1 | 111 |
| user2 | 222 |
| user4 | 444 |
| user5 | 555 |
+----------+----------+
4 rows in set (0.01 sec)
使用查询(读操作)操作在admin端可以看到server11也开启(up)
并行复制
主库一般有多个进程同时访问,就会有多个进程的操作被记录在二进制日志中,但是SLAVE端如果只有1个SQL线程执行回放操作的话,会很慢,时间延迟大,所以要开启多多线程,并由一个线程负责调度;
在server11(slave)上:
mysql> show processlist;
+----+-------------+---------------+------+---------+------+--------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+---------------+------+---------+------+--------------------------------------------------------+------------------+
| 1 | system user | | NULL | Connect | 1156 | Slave has read all relay log; waiting for more updates | NULL |
| 2 | system user | | NULL | Connect | 9313 | Waiting for master to send event | NULL |
| 8 | root | localhost | test | Query | 0 | starting | show processlist |
| 11 | proxy | server6:37401 | test | Sleep | 3856 | | NULL |
+----+-------------+---------------+------+---------+------+--------------------------------------------------------+------------------+
#此时只能看见一个进程
[root@server11 ~]# vim /etc/my.cnf
#添加:
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=16
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log_recovery=ON
[root@server11 ~]# /etc/init.d/mysqld restart
mysql> show processlist;
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
| 1 | system user | | NULL | Connect | 3 | Slave has read all relay log; waiting for more updates | NULL |
| 2 | system user | | NULL | Connect | 5 | Waiting for master to send event | NULL |
| 4 | system user | | NULL | Connect | 5 | Waiting for an event from Coordinator | NULL |
| 5 | system user | | NULL | Connect | 5 | Waiting for an event from Coordinator | NULL |
| 6 | system user | | NULL | Connect | 5 | Waiting for an event from Coordinator | NULL |
| 7 | system user | | NULL | Connect | 5 | Waiting for an event from Coordinator | NULL |
| 8 | system user | | NULL | Connect | 5 | Waiting for an event from Coordinator | NULL |
| 9 | system user | | NULL | Connect | 5 | Waiting for an event from Coordinator | NULL |
| 10 | system user | | NULL | Connect | 5 | Waiting for an event from Coordinator | NULL |
| 11 | system user | | NULL | Connect | 5 | Waiting for an event from Coordinator | NULL |
| 12 | system user | | NULL | Connect | 5 | Waiting for an event from Coordinator | NULL |
| 13 | system user | | NULL | Connect | 5 | Waiting for an event from Coordinator | NULL |
| 14 | system user | | NULL | Connect | 5 | Waiting for an event from Coordinator | NULL |
| 15 | system user | | NULL | Connect | 5 | Waiting for an event from Coordinator | NULL |
| 16 | system user | | NULL | Connect | 5 | Waiting for an event from Coordinator | NULL |
| 19 | system user | | NULL | Connect | 5 | Waiting for an event from Coordinator | NULL |
| 20 | system user | | NULL | Connect | 5 | Waiting for an event from Coordinator | NULL |
| 21 | system user | | NULL | Connect | 5 | Waiting for an event from Coordinator | NULL |
| 22 | root | localhost | NULL | Query | 0 | starting | show processlist |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
此时有16个线程!
参数解释:
slave-parallel-type=LOGICAL_CLOCK #基于组的并行复制方式,默认基于库的并行复制方式
slave-parallel-workers=16 #指定开启的线程数
半同步复制:
主:
mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';
mysql> show variables like '%rpl%';
+-------------------------------------------+------------+
| Variable_name | Value |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled | OFF |
| 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_stop_slave_timeout | 31536000 |
+-------------------------------------------+------------+
slave:
mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
mysql> show plugins;
mysql> show variables like '%rpl%';
+---------------------------------+----------+
| Variable_name | Value |
+---------------------------------+----------+
| rpl_semi_sync_slave_enabled | OFF |
| rpl_semi_sync_slave_trace_level | 32 |
| rpl_stop_slave_timeout | 31536000 |
+---------------------------------+----------+
测试:
slave上:mysql> stop io_thread;
mysql> show slave status\G;
master上:mysql> insert into userdb values ('user7','777');
Query OK, 1 row affected (10.51 sec) #等待了10秒才复制过去
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 | 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 | 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 |
+--------------------------------------------+-------+
slave:mysql> start slave io_thread;
master:
mysql> insert into userdb values ('user8','888');
Query OK, 1 row affected (0.23 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 | 1 |
| Rpl_semi_sync_master_no_tx | 1 |
| Rpl_semi_sync_master_status | ON |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 403 |
| Rpl_semi_sync_master_tx_wait_time | 403 |
| 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 |
+--------------------------------------------+-------+