1.环境：
RHEL6.5 mysql-5.7.17-1.el6.x86_64
Master=server1-172.25.4.1
Slave= server4-172.25.4.4
2.安装mysql
在server1.4上
tar xf mysql-5.7.17-1.el6.x86_64.rpm-bundle.tar
yum install 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
/etc/init.d/mysqld start
如果服务起不来就rm -f /var/lib/mysql
cat /var/log/mysqld.log |grep password
mysql -p
mysql> show database;
ALTER USER root@localhost identified by 'Syx.123456';
3.主从复制基本配置
master server配置
1 )配置 /etc/my.cnf 配置文件
在[mysqld]下添加一下参数
log-bin=mysql-bin 启动二进制日志系统
binlog-do-db=test 二进制需要同步的数据库名
server-id=1 必须为1到232–1之间的一个正整数值
binlog-ignore-db=mysql 避免同步mysql用户配置,以免不必要的麻烦
2)创建同步帐户,并给予权限
mysql -p
mysql>grant replication slave on *.* to test@'172.25.4.%' identified by 'Syx.123456'; #授予从机对test库有复制的权限
mysql>Flush privileges; 刷新数据使其生效
mysql>show master status; 如果是在对库进行操作之后查询状态，就需要去
cd /var/lib/mysql
mysqlbinlog mysql-bin.000001
查询到对数据库进行操作之前的线程id
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 446 | test | mysql | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
记录File和Position的值,下面会用到
3)建立数据库，并建表
mysql> create database test;
mysql> use test
mysql> create table usertb (
-> username varchar(15) not null,
-> password varchar(25) not null);
mysql> desc usertb;
4)/etc/init.d/mysqld restart
slave server 配置
1 ) 配置 /etc/ my.cnf 文件
server-id=2 从服务器ID号,不要和主 ID相同,如果设置多个服
务器,每个从服务器必须有一个唯一的server-id
值,必须与主服务器的以及其它从服务器的不相同。
可以认为server-id值类似于IP地址:这些ID值
能唯一识别复制服务器群集中的每个服务器实例。
master-host=172.25.18.1 指定主服务器 IP 地址
master-user=test 制定在主服务器上可以进行同步的用户名
master-password=test 密码
master-port=3306 同步所用的端口
master-connect-retry=60 断点重新连接时间
replicate-ignore-db=mysql 屏蔽对 mysql 库的同步,以免有麻烦
replicate-do-db=test 同步数据库名称
mysql -p
mysql> show slave status;
mysql> change master to master_host='172.25.4.1',master_user='test',master_password='Syx.123456',master_log_file='mysql-bin.000001',master_log_pos=446;
ERROR 1201 (HY000): Could not initialize master info structure; more error messages can be found in the MySQL error log
如果出现这种报错，是由于新的slave改变了服务端口和文件路径，分析应该是由于mysql-relay-bin.index中仍然保存着旧relay日志文件的路径，而这些路径下又找不到合适的文件，因此报错。重置slave的参照即可，只需要执行下面的命令：
mysql> reset slave;
Query OK, 0 rows affected (0.00 sec)
mysql> start slave；
/etc/init.d/mysqld restart
测试：
mysql> show slave status\G;
.....
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
.....
Slave_IO_Running：连接到主库，并读取主库的日志到本地，生成本地日志文件
Slave_SQL_Running:读取本地日志文件，并执行日志里的SQL命令。
如果数据不同步：
在master
mysqldump -pSyx.123456 --set-gtid-purged=OFF test > test.sql
如果想备份全部文件，就不要红色字体部分，如果加上就是步备份gtid部分
然后将该文件发送到slave上
在这之前要stop slave;
mysql -pSyx.123456 test < test.sql
Start slave；
如果都是yes ,表示从库的I/O,Slave_SQL线程都正确开启.表明数据库正在同步
I/O出错往往时授权问题，SQL出错往往时数据不一致
你也可以通过在master server上的test库中添加数据,看slave server是否同步,建议使用phpmyadmin,比较方便
Master
mysql -p
Mysql> use test
mysql> insert into usertb values ('user1','111');
mysql> insert into usertb values ('user2','222');
mysql> select * from usertb；
Slave
mysql -p
mysql> use test
mysql> select * from usertb；
4.GTID的主从复制（在主从复制配置好的基础上部署比较方便）
Master
Cat auto.cnf #可以看到全局的server-uuid，主从机一样
vim /etc/my.cnf
gtid_mode=ON
enforce-gtid-consistency=true
/etc/init.d/mysqld restart
mysql -p
use test
Insert into usertb value (‘user3’,’6666’);
Slave
vim /etc/my.cnf
gtid_mode=ON
enforce-gtid-consistency=true
/etc/init.d/mysqld restart
mysql -p
mysql> stop slave;
mysql> change master to master_host='172.25.4.1',master_user='test',master_password='Syx.123456',MASTER_AUTO_POSITION=1;
mysql> start slave;
mysql> set global read-only=on; #全局变量只读
mysql> select *from test.usertb; #数据已经同步
mysql> use mysql
mysql> show tables;
mysql> select * from gtid_executed;
5.并行复制（又叫多线程复制）
基于库的并行复制（单库多表，更多见）--mysql5.6以前
基于组的并行复制，用表的方式存储--从mysql5.7开始
Slave
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
/etc/init.d/mysqld restart
mysql> show processlist; ##可以看到16个并发worker
6.半同步复制
（基于异步的，如果半同步失败，自动转换成异步，异步效率最高，但是无法完全保证数据的一致性，但是全同步和半同步可以相对保证数据的一致性。）
Master
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so'; 添加半同步模块，并重命名
slave
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
mysql> show variables like '%semi_sync%' 查看半同步的参数状态
+-------------------------------------------+------------+
| 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 |
+-------------------------------------------+------------+
timeout #半同步等待时间10秒，10秒后没收到同步信息就会转会异步
| rpl_semi_sync_master_wait_for_slave_count | 1 |
等待slave成功同步的主机数
mysql> set global rpl_semi_sync_slave_enabled=1;
Master
mysql> show variables like '%semi_sync%'
mysql> set global rpl_semi_sync_master_enabled=1;
mysql> show status like '%semi_sync%'; （只能在mater上看到参数）
+--------------------------------------------+-------+
| 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 | 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 | 4297 |
| Rpl_semi_sync_master_tx_wait_time | 4297 |
| 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 |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_no_tx | 1 |
半同步失败次数
| Rpl_semi_sync_master_yes_tx | 1 |
半同步成功次数
Slave
mysql> stop slave io_thread; #关掉IO线程，如果超过10秒没有启用，就会转为异步。
mysql> start slave io_thread; #再次启用，则转为半同步
7.读写分离
添加调度器Proxy :server2-172.25.4.2
tar zxf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz
mv mysql-proxy-0.8.5-linux-el6-x86-64bit /usr/local/
cd /usr/local/
ln -s mysql-proxy-0.8.5-linux-el6-x86-64bit/ mysql-proxy
mkdir etc
cd /etc
vim mysql-proxy.conf
[mysql-proxy]
user=root
daemon=true
keepalive=true
log-level=debug
log-file=/usr/local/mysql-proxy/log/mysql-proxy.log
admin-username=admin
admin-password=westos
admin-lua-script=/usr/local/mysql-proxy/lib/mysql-proxy/lua/admin.lua
#proxy-address=172.25.4.2:3306
proxy-backend-addresses=172.25.4.1:3306
proxy-read-only-backend-addresses=172.25.4.4:3306
plugins=proxy,admin
mkdir /usr/local/mysql-proxy/log
cd /usr/local/mysql-proxy/share/doc/mysql-proxy
vim rw-splitting.lua
40 min_idle_connections = 1, 最小连接数
41 max_idle_connections = 2, 最大连接数
proxy
/usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/etc/mysql-proxy.conf #启动调度服务
如果出现以下错误：
2017-08-04 03:06:48: (critical) mysql-proxy-cli.c:326: loading config from '/usr/local/mysql-proxy/etc/mysql-proxy.conf' failed: permissions of /usr/local/mysql-proxy/etc/mysql-proxy.conf aren't secure
(0660 or stricter required)
提示这个文件权限应该为660
cd /usr/local/mysql-proxy/etc
chmod 660 mysql-proxy.conf
killall mysql-proxy ##如果配置文件有问题，要先停掉服务，在重启
Master上
mysql> grant insert, select, update on *.* to admin@'172.25.4.%' identified by 'Syx.123456';
slave上
mysql> stop slave;
mysql> grant insert, select, update on *.* to admin@'172.25.4.%' identified by 'Syx.123456';
mysql> start slave;
mysql> show slave status\G;
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
mysql> set global read_only=1; ##打开只读模式(flush tables with read lock; 锁表 unlock tables; 解表)
测试：
proxy
mysql -h 172.25.4.2 -P 4040 -u admin -p 访问后端的调度
mysql> use test
mysql> select * from usertb;
#mysql -h 172.25.4.2 -P 4041 -u admin -p 访问管理用户的调度
mysql> select * from backends;
+-------------+-------------------+---------+------+------+-------------------+
| backend_ndx | address | state | type | uuid | connected_clients |
+-------------+-------------------+---------+------+------+-------------------+
| 1 | 172.25.4.1:3306 | up | rw | NULL | 0 |
| 2 | 172.25.4.4:3306 | up | ro | NULL | 0 |
+-------------+-------------------+---------+------+------+-------------------+
测试读写分离:
<1>确认半同步复制没有错误,即确认slave是2个Yes,在master和slave上安装lsof用于查看连接的主机
yum install -y lsof #显示当前操作系统打开的文件
master:
# lsof -i :3306
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
mysqld 2217 mysql 33u IPv6 11010 0t0 TCP *:mysql (LISTEN)
mysqld 2217 mysql 49u IPv6 11704 0t0 TCP server1:mysql->server2:45796 (ESTABLISHED)
mysqld 2217 mysql 53u IPv6 11151 0t0 TCP server1:mysql->server4:40416 (ESTABLISHED)
slave:
# lsof -i :3306
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
mysqld 2760 mysql 17u IPv6 11407 0t0 TCP *:mysql (LISTEN)
mysqld 2760 mysql 54u IPv4 11584 0t0 TCP server2:40416->server1:mysql (ESTABLISHED)
<2>在server2上进行mysql读写操作,测试读写是否分离
server2:
# mysql -h 172.25.4.2 -P 3306 -u admin -p
mysql> select * from test.usertb; ##查看数据（读操作）
此时再查看slave上的连接:
slave:
# lsof -i :3306
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
mysqld 2760 mysql 17u IPv6 11407 0t0 TCP *:mysql (LISTEN)
mysqld 2760 mysql 49u IPv6 12712 0t0 TCP server4:mysql->server2:41194 (ESTABLISHED) ##新的连接来自server2,即读操作被调度到slave
mysqld 2760 mysql 54u IPv4 11584 0t0 TCP server4:40416->server1:mysql (ESTABLISHED)
再在server2进行数据的写入
server2:
mysql> insert into test.usertb values ('user6','666');
再查看master上的连接:
master:
# lsof -i :3306
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
mysqld 2217 mysql 33u IPv6 11010 0t0 TCP *:mysql (LISTEN)
mysqld 2217 mysql 49u IPv6 11704 0t0 TCP server1:mysql->server2:45796 (ESTABLISHED)
mysqld 2217 mysql 52u IPv6 11786 0t0 TCP server1:mysql->server2:45808 (ESTABLISHED) ##新的lianjie来自server2,写操作备调度到master
mysqld 2217 mysql 53u IPv6 11151 0t0 TCP server1:mysql->server4:40416 (ESTABLISHED)
测试结果:server2上对mysql的读操作是在slave上进行的，写操作是在master上进行的，实现了mysql的读写分离。（还可以用真机测试，篇幅的原因真机测试就不写了）
8.慢查询
mysqlbinlog mysql-bin.000006 --base64-output=DECODE-ROWS -vv 查看修改过的数据库加密内容
mysql> set binlog_row_image=minimal;
mysql> set global binlog_row_image=minimal;
修改参数，使日志只记录，修改过的数据库内容
mysql> update test.usertb set password='123';
更新数据库数据，进行测试
mysql> set global slow_query_log=1;
mysql> show variables like '%slow%'; 查看慢查询的属性
mysql> show status like '%slow%'; 查看慢查询的状态
mysql> select sleep(10); 设置一条慢查询
mysql> show status like '%slow%';
+---------------------+-------+
| Variable_name | Value |
+---------------------+-------+
| Slow_launch_threads | 0 |
| Slow_queries | 1 |
+---------------------+-------+
2 rows in set (0.01 sec)
cat /var/lib/mysql/server1-slow.log 查看筛选出来的慢查询语句
/usr/sbin/mysqld, Version: 5.7.17-log (MySQL Community Server (GPL)). started with:
Tcp port: 3306 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
# Time: 2017-08-06T06:50:36.932388Z
# User@Host: root[root] @ localhost [] Id: 38
# Query_time: 10.005069 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
SET timestamp=1502002236;
select sleep(10);
通过筛选出慢查询语句，从而做出优化
mysql> show variables like '%query%';
long_query_time | 10.000000 #可以设置你的慢查询判断时间阀门
Percona-xtrabackup 支持在不锁表的情况下备份
Mysqldump 备份必须锁表
9.分布式集群
（为了保证所有节点数据一致，全同步复制）
Master上
/etc/init.d/mysqld stop
rm -fr * /varserver-id=1
vim /etc/my.cnf
server-id=1
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="292b82bc-7a7c-11e7-8954-525400542c39"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address="172.25.4.1:24901"
loose-group_replication_group_seeds="172.25.4.1:24901,172.25.4.3:24901,172.25.4.4:24901"
loose-group_replication_bootstrap_group=off
loose-group_replication_single_primary_mode=FALSE
loose-group_replication_enforce_update_everywhere_checks=TRUE
loose 在第一次加载replication模块时需要加上，不然系统不识别参数，无法加载模块
/etc/init.d/mysqld start
cat /var/log/mysqld.log |grep password ##查看密码
mysql -p
mysql> SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)
mysql> alter user root@localhost identified by 'Syx.123456';
Query OK, 0 rows affected (0.00 sec)
mysql> select uuid(); 可以用此方式随便查询一个数据库的UUID然后添加到/etc/my.cnf的配置文件里面
+--------------------------------------+
| uuid() |
+--------------------------------------+
| 6b01105d-7a83-11e7-a8e3-5254001481aa |
+--------------------------------------+
1 row in set (0.00 sec)
mysql> SET SQL_LOG_BIN=0;
mysql> GRANT REPLICATION SLAVE ON *.* TO test@'%' IDENTIFIED BY 'Syx.123456';
mysql> FLUSH PRIVILEGES;
mysql> SET SQL_LOG_BIN=1;
mysql> CHANGE MASTER TO MASTER_USER='test', MASTER_PASSWORD='Syx.123456' FOR CHANNEL 'group_replication_recovery';
mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';
mysql> show plugins;
mysql> SET GLOBAL group_replication_bootstrap_group=ON;
mysql> STOP GROUP_REPLICATION;
mysql> SET GLOBAL group_replication_ip_whitelist="172.25.4.0/24,127.0.0.1/8";
mysql> START GROUP_REPLICATION;
mysql> SET GLOBAL group_replication_bootstrap_group=OFF;
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | a821bdf5-7a82-11e7-9348-5254001481aa | server1 | 3306 | ONLINE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
1 row in set (0.00 sec)
mysql> create database test;
mysql > use test
mysql > create table t1 (c1 int primary key,c2 text not null);
mysql > insert into t1 values (1,’Luis’);
mysql > select * from t1;
Slave
/etc/init.d/mysqld stop
rm -fr * /var/lib/mysql/
vim /etc/my.cnf
server-id=2
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="292b82bc-7a7c-11e7-8954-525400542c39"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address="172.25.4.1:24901"
loose-group_replication_group_seeds="172.25.4.1:24901,172.25.4.3:24901,172.25.4.4:24901"
loose-group_replication_bootstrap_group=off
loose-group_replication_single_primary_mode=FALSE
loose-group_replication_enforce_update_everywhere_checks=TRUE
Loose-group_replication_ip_whitelist="172.25.4.0/24,127.0.0.1/8"
Loose-group_replication_allow_local_disjoint_gtids_join=ON
/etc/init.d/mysqld start
cat /var/log/mysqld.log |grep password
mysql> SET SQL_LOG_BIN=0；
mysql> alter user root@localhost identified by 'Syx.123456';
mysql> GRANT REPLICATION SLAVE ON *.* TO test@'%' IDENTIFIED BY 'Syx.123456';
mysql> FLUSH PRIVILEGES;
mysql> SET SQL_LOG_BIN=1;
mysql> CHANGE MASTER TO MASTER_USER='test', MASTER_PASSWORD='Syx.123456' FOR CHANNEL 'group_replication_recovery';
mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';
mysql> SET GLOBAL group_replication_ip_whitelist="172.25.4.0/24,127.0.0.1/8";
set global group_replication_allow_local_disjoint_gtids_join=ON;
Mysql> uninstall plugin validate_password;
mysql> START GROUP_REPLICATION;
mysql> SELECT * FROM performance_schema.replication_group_members;
测试：
可以在任意一个节点上，插入数据
mysql> INSERT INTO test.t1 VALUES (3,’hello’);
再去别的节点查看，是否更新
401
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
