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)