mysql:
大规模，高并发web服务器体系结构:
MySQL复制, Nginx, LNMP, Memcached, Tomcat(java, servlet, varnish(squid)
NoSQL(redis, mongodb)
二进制日志
事物日志
错误日志
一般查询日志
中继日志
慢查询日志
二进制日志：
数据目录
mysql-bin.XXXXX
滚动: 达到最大上限，flush logs, 服务器重启
mysql> PURGE（清除二进制日志）
二进制日志的格式：
statement 基于语句
row 基于行
mixed 混合的
mysql-bin.index: 二进制日志文件索引
mysql> SHOW MASTER STATUS;（查看当前正在使用的二进制日志文件）
mysql> SHOW BINARY LOGS;（查看mysql上所仍然存在的二进制日志文件列表）
mysql> SHOW BINLOG EVENT IN "file";（查看对应二进制日志文件内容）
event:
timestamp 时间戳
position,（位置） offset（偏移量）, OPERATION, server-id
事件本身
即使点还原:
MySQL: tx1
MySQL隔离级别:
READ-UNCOMMITTED 读未提交
READ-COMMITTED 读提交
REPEATABLE-READ 重读
SERTALIZABLE 可串行化
MySQL Replication（复制）
复制的作用：
辅助实习备份
高可用
异地容灾
scale out: 分摊负载
rw-spliting 读写分离
blocakhole 引擎，不用保存数据；
主从架构中，不使用MySQL代理，如何让主的负责写，从的负责读？
server-id
双主，无法实习平均负载写操作；
双主模型：
tutors: name, age, gender, tid
tom 10
jerry 30
A: UPDATE tutors SET name=jerry WHERE age=10;
B: UPDATE tutors SET age=30 WHERE name=to;
scale out
分库
scale on
垂直拆分
水平拆分
读写分离:
mysql-proxy
amoeba
数据拆分:
cobar:
master: slave
1-->N
slave: master
1-->N X
一个从只能属于一个主服务器；
MySQL 5.5:
MySQL 5.6: gtid（全局事物号，更加安全，不会在多数据并行执行产生数据混乱）, multi-thread replication（多线程复制）
配置MySQL复制基本步骤：
一、master
1、启用二进制日志
log-bin = master-bin
log-bin-index = master-bin.index
2、选择一个唯一的server-id
server-id = {0-2^32-1}
3、创建具有复制权限的用户
REPLTCATION SLAVE（复制的从节点，从主服务器二进制日志文件中复制事件的权限）
REPLICATION CLIENT（具有连接主服务器获取相关信息的权限）
二、slave
1、启用中继日志
realy-log = relay-log
relay-log-index =
2、选择一个唯一的server-id（不能和主服务器重复）
server-id = {0-2^32-1}
3、连接至主服务器，并开始复制数据；
mysql> CHANGER MASTER TO MASTER_HOST ='',MASTER_PORT='',MASTER_LOG_FILE='',MASTER_LOG_POS='',MASTER_USER='',MASTER_PASSWOR='';
mysql> START SLAVE;（启动从服务器线程）
mysql> START SLAVE IO_Thread;
mysql> START SLAVE SQL_Thread;
复制线程：
master: dump
slave: IO_Thread, SQL_Thread
ssl
read-only = YES（让mysql服务器只读）
在从服务器设定，但对具有SUPER权限的用户不生效；
sync_binlog = ON
在主服务器上设定，用于事物安全
percoma: percona-tools
mattkit-tools
SSL:
Dual MASTER:
设置半同步步骤：
在Master和Slave的mysql命令行运行如下代码：
# On Master
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;
mysql> SET GLOBAL rpl_semi_sync_master_timeout = 1000;
# On Slave
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
mysql> SET GLOBAL rpl_semi_sync_slave_enabled = 1;
mysql> STOP SLAVE IO_THREAD; START SLAVE IO_THREAD;
在Master和Slave的my.cnf中编辑：
# On Master
[mysqld]
rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_timeout=1000 # 1 second
# On Slave
[mysqld]
rpl_semi_sync_slave_enabled=1
# 也可通过设置全局变量的方式来设置,如下：
set global rpl_semi_sync_master_enabled=1
# 取消加载插件
mysql> UNINSTALL PLUGIN rpl_semi_sync_master;
==============================================
查看从服务器上的semi_sync是否开启:
mysql> SHOW GLOBAL STATUS LIKE 'rpl_semi%';
查看主服务器上的semi_sync是否开启，注意clients 变为1 ，证明主从半同步复制连接成功:
mysql> SHOW GLOBAL STATUS LIKE 'rpl_semi%';
6、主服务器崩溃，事务已经提交-->写入二进制日志；
在主-从架构上建议使用的配置：
主服务器：
sync_binlog=1
innodb_flush_logs_at_trx_commit=1
从服务器：
skip_slave_start=1
read_only=1
设置主-主复制：
1、在两台服务器上各自建立一个具有复制权限的用户；
2、修改配置文件：
# 主服务器上
[mysqld]
server-id = 10
log-bin = mysql-bin
relay-log = relay-mysql
relay-log-index = relay-mysql.index
auto-increment-increment = 2（每一个跳2个，1、3、5...）
auto-increment-offset = 1（从1开始，表中有很多字段是会自动增长的，为了避免两个数据库中将来两个表合并数据发生冲突，一个库应该使用奇数，一个库使用偶数）
# 从服务器上
[mysqld]
server-id = 20
log-bin = mysql-bin
relay-log = relay-mysql
relay-log-index = relay-mysql.index
auto-increment-increment = 2（每一个跳2个，2、4、6...）
auto-increment-offset = 2（从2开始）
3、如果此时两台服务器均为新建立，且无其它写入操作，各服务器只需记录当前自己二进制日志文件及事件位置，以之作为另外的服务器复制起始位置即可
server1|mysql> SHOW MASTER STATUS\G
************************** 1. row ***************************
File: mysql-bin.000001
Position: 710
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)
server2|mysql> SHOW MASTER STATUS\G
mysql> SHOW MASTER STATUS\G
*************************** 1. row ***************************
File: mysql-bin.000003
Position: 811
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)
4、各服务器接下来指定对另一台服务器为自己的主服务器即可：
server1|mysql> CHANGE MASTER TO ...,MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=811
server2|mysql> CHANGE MASTER TO ...,MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=710
node1:
[root@localhost ~]# ifconfig eth0
eth0 Link encap:Ethernet HWaddr 00:0C:29:CC:FA:AE
inet addr:172.16.100.6 Bcast:172.16.100.255 Mask:255.255.255.0
inet6 addr: fe80::20c:29ff:fecc:faae/64 Scope:Link
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
RX packets:101 errors:0 dropped:0 overruns:0 frame:0
TX packets:121 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:14104 (13.7 KiB) TX bytes:18823 (18.3 KiB)
Interrupt:67 Base address:0x2000
[root@localhost ~]# ntpdate 172.16.100.30（向ntp服务器同步事件）
18 Jun 21:42:10 ntpdate[13711]: step time server 172.16.100.30 offset -183.462819 sec
[root@localhost ~]# hostname node1.magedu.com（修改主机名称）
[root@localhost ~]# vim /etc/sysconfig/network（编辑主机名配置文件）
NETWORKING=yes
NETWORKING_IPV6=yes
HOSTNAME=node1.magedu.com
[root@localhost ~]# uname -n（查看主机名）
node1.magedu.com
[root@node1 ~]# vim /etc/hosts（编辑本地解析文件）
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1 localhost.localdomain localhost
::1 localhost6.localdomain6 localhost6
172.16.100.6 node1.magedu.com node1
172.16.100.7 node2.magedu.com node2
[root@node1 ~]# ssh-keygen -t rsa -f ~/.ssh/id_rsa -P ''（生存一对密钥，-t制定加密算法类型rsa或dsa，-f制定撕咬文件保存位置，-P制定私钥密码）
Generating public/private rsa key pair.
Created directory '/root/.ssh'.
Your identification has been saved in /root/.ssh/id_rsa.
Your public key has been saved in /root/.ssh/id_rsa.pub.
The key fingerprint is:
80:bd:e8:23:5c:1c:f3:ce:53:97:36:5a:a5:59:88:82 root@node1.magedu.com
[root@node1 ~]# ssh-copy-id -i .ssh/id_rsa.pub root@172.16.100.7（通过ssh-copy-id将.ssh/id_rsa.pub公钥文件复制到远程主机172.16.100.7，以root用户登录，
-i制定公钥文件）
15
The authenticity of host '172.16.100.7 (172.16.100.7)' can't be established.
RSA key fingerprint is 89:76:bc:a3:db:68:83:e1:20:ce:d4:69:eb:73:0d:f1.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '172.16.100.7' (RSA) to the list of known hosts.
root@172.16.100.7's password:
Now try logging into the machine, with "ssh 'root@172.16.100.7'", and check in:
.ssh/authorized_keys
to make sure we haven't added extra keys that you weren't expecting.
node2:
[root@localhost ~]# ifconfig eth0（查看eth0网卡信息）
eth0 Link encap:Ethernet HWaddr 00:0C:29:B8:44:39
inet addr:172.16.100.7 Bcast:172.16.100.255 Mask:255.255.255.0
inet6 addr: fe80::20c:29ff:feb8:4439/64 Scope:Link
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
RX packets:1436 errors:0 dropped:0 overruns:0 frame:0
TX packets:1535 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:121917 (119.0 KiB) TX bytes:153886 (150.2 KiB)
Interrupt:67 Base address:0x2000
[root@localhost ~]# ntpdate 172.16.100.30（向ntp服务器同步时间）
18 Jun 21:49:18 ntpdate[17789]: adjust time server 172.16.100.30 offset -0.000161 sec
[root@localhost ~]# hostname node2.magedu.com（修改主机名）
[root@localhost ~]# hostname（查看主机名）
node2.magedu.com
[root@localhost ~]# vim /etc/sysconfig/network（修改主机名配置文件）
NETWORKING=yes
NETWORKING_IPV6=yes
HOSTNAME=node2.magedu.com
[root@localhost ~]# uname -n（查看主机名）
node2.magedu.com
[root@node2 ~]# vim /etc/hosts（编辑本机解析配置文件）
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1 localhost.localdomain localhost
::1 localhost6.localdomain6 localhost6
172.16.100.6 node1.magedu.com node1
172.16.100.7 node2.magedu.com node2
[root@node2 ~]# ssh-keygen -t rsa -f ~/.ssh/id_rsa -P ''（生成一对密钥，-t制定加密算法类型rsa或dsa，-f制定密钥文件保存位置，-P指定私钥密码）
Generating public/private rsa key pair.
Your identification has been saved in /root/.ssh/id_rsa.
Your public key has been saved in /root/.ssh/id_rsa.pub.
The key fingerprint is:
9a:7b:ff:9b:d8:dc:24:8d:ef:4c:39:34:83:30:21:a5 root@node2.magedu.com
[root@node2 ~]# ssh-copy-id -i .ssh/id_rsa.pub root@172.16.100.6（通过ssh-copy-id将.ssh/id_rsa.pub公钥文件复制到远程主机172.16.100.6，以root用户登录，
-i制定公钥文件）
15
The authenticity of host '172.16.100.6 (172.16.100.6)' can't be established.
RSA key fingerprint is ea:32:fd:b5:e6:d2:75:e2:c2:c2:8c:63:d4:82:4c:48.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '172.16.100.6' (RSA) to the list of known hosts.
root@172.16.100.6's password:
Now try logging into the machine, with "ssh 'root@172.16.100.6'", and check in:
.ssh/authorized_keys
to make sure we haven't added extra keys that you weren't expecting.
node1:
[root@node1 ~]# mkdir -pv /mydata/data（创建/mydata/data，-p递归创建，-v显示创建过程） mkdir: created directory `/mydata' mkdir: created directory `/mydata/data' [root@node1 ~]# useradd -r mysql（创建mysql用户） [root@node1 ~]# chown -R mysql.mysql /mydata/data/（更改/mydata/data属主属组为mysql） [root@node1 ~]# lftp 172.16.0.1（连接ftp服务器） lftp 172.16.0.1:~> cd pub/Sources/mysql-5.5/ lftp 172.16.0.1:/pub/Sources/mysql-5.5> get mysql-5.5.28-linux2.6-i686.tar.gz（下载mysql-5.5.28） 179907710 bytes transferred in 5 seconds (37.46M/s) lftp 172.16.0.1:/pub/Sources/mysql-5.5> bye（退出） [root@node1 ~]# tar xf mysql-5.5.28-linux2.6-i686.tar.gz -C /usr/local/（解压mysql-5.5.28，x解压，f后面跟文件名，-C更改解压目录） [root@node1 ~]# cd /usr/local/（切换到？usr/local目录） [root@node1 local]# ls（查看当前目录文件及子目录） bin etc games include lib libexec mysql-5.5.28-linux2.6-i686 sbin share src [root@node1 local]# ln -sv mysql-5.5.28-linux2.6-i686 mysql（给mysql-5.5.28创建软连接，-s软连接，-v显示创建过程，） create symbolic link `mysql' to `mysql-5.5.28-linux2.6-i686' [root@node1 local]# cd mysql（切换到mysql目录） [root@node1 mysql]# chown -R root.mysql ./*（更改当前目录所有文件的的属主为root，属组为mysql，-R递归更改） [root@node1 mysql]# ll（查看当前目录文件及子目录） total 132 drwxr-xr-x 2 root mysql 4096 Jun 18 22:08 bin -rw-r--r-- 1 root mysql 17987 Aug 29 2012 COPYING drwxr-xr-x 4 root mysql 4096 Jun 18 22:08 data drwxr-xr-x 2 root mysql 4096 Jun 18 22:08 docs drwxr-xr-x 3 root mysql 4096 Jun 18 22:08 include -rw-r--r-- 1 root mysql 7604 Aug 29 2012 INSTALL-BINARY drwxr-xr-x 3 root mysql 4096 Jun 18 22:08 lib drwxr-xr-x 4 root mysql 4096 Jun 18 22:08 man drwxr-xr-x 10 root mysql 4096 Jun 18 22:08 mysql-test -rw-r--r-- 1 root mysql 2552 Aug 29 2012 README drwxr-xr-x 2 root mysql 4096 Jun 18 22:08 scripts drwxr-xr-x 27 root mysql 4096 Jun 18 22:08 share drwxr-xr-x 4 root mysql 4096 Jun 18 22:08 sql-bench drwxr-xr-x 2 root mysql 4096 Jun 18 22:08 support-files [root@node1 mysql]# scripts/mysql_install_db --user=mysql --datadir=/mydata/data/（初始化mysql，--user指定运行mysql用户，--datadir指定数据目录） Installing MySQL system tables... OK Filling help tables... OK To start mysqld at boot time you have to copy support-files/mysql.server to the right place for your system PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! To do so, start the server, then issue the following commands: ./bin/mysqladmin -u root password 'new-password' ./bin/mysqladmin -u root -h node1.magedu.com password 'new-password' Alternatively you can run: ./bin/mysql_secure_installation which will also give you the option of removing the test databases and anonymous user created by default. This is strongly recommended for production servers. See the manual for more instructions. You can start the MySQL daemon with: cd . ; ./bin/mysqld_safe & You can test the MySQL daemon with mysql-test-run.pl cd ./mysql-test ; perl mysql-test-run.pl Please report any problems with the ./bin/mysqlbug script! [root@node1 mysql]# cp support-files/my-large.cnf /etc/my.cnf（复制mysql配置文件到/etc目录叫my.cnf） [root@node1 mysql]# cp support-files/mysql.server /etc/rc.d/init.d/mysqld（复制mysql启动脚本到/etc/rc.d/init.d叫mysqld） [root@node1 mysql]# chkconfig --add mysqld（将mysqld添加为系统服务） [root@node1 mysql]# vim /etc/my.cnf（编辑my.cnf配置文件） log-bin=master-bin（启用二进制日志功能） log-bin-index=master-bin.index（指定二进制日志索引） binlog_format=mixed（指定二进制日志格式） server-id = 1（指定server-id） innodb_file_per_table = 1（启动对innodb，每张表使用独立的文件） data_dir = /mydata/data（指定数据文件目录） [root@node1 mysql]# service mysqld start（启动mysqld服务） Starting MySQL.The server quit without updating PID file (/[FAILED]l/mysql/data/node1.magedu.com.pid). 提示:启动失败； [root@node1 mysql]# ls /mydata/data/（查看/mydata/data目录文件及子目录） mysql performance_schema test [root@node1 mysql]# vim /etc/my.cnf（编辑my.cnf配置文件） datadir = /mydata/data（制定数据文件目录） [root@node1 mysql]# service mysqld start（启动mysqld服务） Starting MySQL... [ OK ] [root@node1 mysql]# vim /etc/profile.d/mysqld.sh（编辑mysqld.sh环境变量） export PATH=$PATH:/usr/local/mysql/bin [root@node1 mysql]# . /etc/profile.d/mysqld.sh（读取mysqld.sh脚本） [root@node1 mysql]# mysql（连接mysql服务器） Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.5.28-log MySQL Community Server (GPL) Copyright (c) 2000, 2012, 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> \q（退出） Bye [root@node1 mysql]# cd（切换到用户家目录） [root@node1 ~]# scp mysql-5.5.28-linux2.6-i686.tar.gz node2:/root/（复制mysql-5.5.28到node2主机的/root目录） mysql-5.5.28-linux2.6-i686.tar.gz 100% 172MB 21.5MB/s 00:08 [root@node1 ~]# mysql（连接mysql） Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.5.28-log MySQL Community Server (GPL) Copyright (c) 2000, 2012, 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> GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'172.16.%.%' IDENTIFIED BY 'replpass';（授权repluser用户172.16.0.0网络主机备份复制所有库所有表权限 ，用户密码为replpass） Query OK, 0 rows affected (0.01 sec) mysql> FLUSH PRIVILEGES;（刷新授权表） Query OK, 0 rows affected (0.00 sec) mysql> \q（退出） Bye
node2:
[root@node2 ~]# mkdir -pv /mydata/data（创建/mydata/data，-p递归创建，-v显示创建过） mkdir: 已创建目录 “/mydata” mkdir: 已创建目录 “/mydata/data” [root@node2 ~]# useradd -r mysql（添加mysql用户） [root@node2 ~]# chown -R mysql.mysql /mydata/data/（更改/mydata/data属主属组为mysql） [root@node2 ~]# tar xf mysql-5.5.28-linux2.6-i686.tar.gz -C /usr/local/（解压mysql-5.5.28，x解压，f后面跟文件名，-C更改解压目录）
node1:
[root@node1 ~]# scp /etc/my.cnf node2:/etc/（复制my.cnf到node2主机/etc目录） my.cnf 100% 4746 4.6KB/s 00:00
node2:
[root@node2 ~]# cd /usr/local/（切换到/usr/local目录） [root@node2 local]# ln -sv mysql-5.5.28-linux2.6-i686 mysql（给mysql-5.5.28创建软连接，-s软连接，-v显示创建过程，） 创建指向“mysql-5.5.28-linux2.6-i686”的符号链接“mysql” [root@node2 local]# cd mysql（切换到mysql目录） [root@node2 mysql]# chown -R root.mysql ./*（修改当前目录下所有文件的属主为root，属组为mysql，-R递归更改） [root@node2 mysql]# ll（查看当前目录文件及子目录详细信息） 总计 132 drwxr-xr-x 2 root mysql 4096 06-19 01:03 bin -rw-r--r-- 1 root mysql 17987 2012-08-29 COPYING drwxr-xr-x 4 root mysql 4096 06-19 01:02 data drwxr-xr-x 2 root mysql 4096 06-19 01:03 docs drwxr-xr-x 3 root mysql 4096 06-19 01:03 include -rw-r--r-- 1 root mysql 7604 2012-08-29 INSTALL-BINARY drwxr-xr-x 3 root mysql 4096 06-19 01:02 lib drwxr-xr-x 4 root mysql 4096 06-19 01:03 man drwxr-xr-x 10 root mysql 4096 06-19 01:03 mysql-test -rw-r--r-- 1 root mysql 2552 2012-08-29 README drwxr-xr-x 2 root mysql 4096 06-19 01:03 scripts drwxr-xr-x 27 root mysql 4096 06-19 01:03 share drwxr-xr-x 4 root mysql 4096 06-19 01:02 sql-bench drwxr-xr-x 2 root mysql 4096 06-19 01:03 support-files [root@node2 mysql]# scripts/mysql_install_db --user=mysql --datadir=/mydata/data/（初始化mysql，--user指定启动mysql的用户，--datadir指定数据文件目录） Installing MySQL system tables... OK Filling help tables... OK To start mysqld at boot time you have to copy support-files/mysql.server to the right place for your system PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! To do so, start the server, then issue the following commands: ./bin/mysqladmin -u root password 'new-password' ./bin/mysqladmin -u root -h node2.magedu.com password 'new-password' Alternatively you can run: ./bin/mysql_secure_installation which will also give you the option of removing the test databases and anonymous user created by default. This is strongly recommended for production servers. See the manual for more instructions. You can start the MySQL daemon with: cd . ; ./bin/mysqld_safe & You can test the MySQL daemon with mysql-test-run.pl cd ./mysql-test ; perl mysql-test-run.pl Please report any problems with the ./bin/mysqlbug script! [root@node2 mysql]# cp support-files/mysql.server /etc/init.d/mysqld（复制mysql服务脚本到/etc/init.d目录叫mysqld） [root@node2 mysql]# chkconfig --add mysqld（将mysqld添加到服务列表） [root@node2 mysql]# vim /etc/my.cnf（编辑my.cnf配置文件） #log-bin=master-bin #log-bin-index=master-bin.index relay-log = relay-log（启用中继日志） relay-log-index = relay-log.index server-id = 11（修改server id为11，不能和主节点mysql的server id相同） [root@node2 mysql]# service mysqld start（启动mysqld服务） Starting MySQL... [确定] [root@node2 mysql]# vim /etc/profile.d/mysql.sh（修改环境变量） export PATH=$PATH:/usr/local/mysql/bin [root@node2 mysql]# . /etc/profile.d/mysql.sh（读取mysql.sh脚本） [root@node2 mysql]# mysql（连接mysql） Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.5.28 MySQL Community Server (GPL) Copyright (c) 2000, 2012, 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（查看备用状态） Empty set (0.00 sec) mysql> \q（退出） Bye
node1:
[root@node1 ~]# mysql（连接mysql） Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.5.28-log MySQL Community Server (GPL) Copyright (c) 2000, 2012, 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 MASTER STATUS;（查看当前正在使用的二进制日志文件） +-------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +-------------------+----------+--------------+------------------+ | master-bin.000001 | 499 | | | +-------------------+----------+--------------+------------------+ 1 row in set (0.00 sec)
mysql> SHOW BINLOG EVENTS IN 'master-bin.000001';（查看master-bin.000001二进制日志产生的事件） +-------------------+-----+-------------+-----------+-------------+--------------------------------------------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +-------------------+-----+-------------+-----------+-------------+--------------------------------------------------------------------------------------+ | master-bin.000001 | 4 | Format_desc | 1 | 107 | Server ver: 5.5.28-log, Binlog ver: 4 | | master-bin.000001 | 107 | Query | 1 | 265 | GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'.16.%.%' IDENTIFIED BY 'replpass' | | master-bin.000001 | 265 | Query | 1 | 424 | GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'2.16.%.%' IDENTIFIED BY 'replpass' | | master-bin.000001 | 424 | Query | 1 | 499 | FLUSH PRIVILEGES | | master-bin.000001 | 499 | Query | 1 | 656 | GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'172.16.%.%' IDENTIFIED BY 'replpass' | +-------------------+-----+-------------+-----------+-------------+--------------------------------------------------------------------------------------+ 5 rows in set (0.00 sec) 提示：656位置前面执行的操作对于备用mysql服务器没有任何用处，所以复制从656以后开始；
node2:
[root@node2 mysql]# mysql（连接mysql）
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.28 MySQL Community Server (GPL)
Copyright (c) 2000, 2012, 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> CHANGE MASTER TO MASTER_HOST='172.16.100.6',MASTER_USER='repluser',MASTER_PASSWORD='replpass',MASTER_LOG_FILE='master-bin.000001',
MASTER_LOG_FILE_POS=656;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right sy
ntax to use near 'MASTER_LOG_FILE_POS=656' at line 1
mysql> HELP change（查看change命令的帮助）
Many help items for your request exist.
To make a more specific request, please type 'help <item>',
where <item> is one of the following
topics:
ALTER TABLE
CHANGE MASTER TO
mysql> HELP change MASTER TO（查看change MASTER TO命令帮助）
Name: 'CHANGE MASTER TO'
Description:
Syntax:
CHANGE MASTER TO option [, option] ...
option:
MASTER_BIND = 'interface_name'
| MASTER_HOST = 'host_name'
| MASTER_USER = 'user_name'
| MASTER_PASSWORD = 'password'
| MASTER_PORT = port_num
| MASTER_CONNECT_RETRY = interval
| MASTER_HEARTBEAT_PERIOD = interval
| MASTER_LOG_FILE = 'master_log_name'
| MASTER_LOG_POS = master_log_pos
| RELAY_LOG_FILE = 'relay_log_name'
| RELAY_LOG_POS = relay_log_pos
| MASTER_SSL = {0|1}
| MASTER_SSL_CA = 'ca_file_name'
| MASTER_SSL_CAPATH = 'ca_directory_name'
| MASTER_SSL_CERT = 'cert_file_name'
| MASTER_SSL_KEY = 'key_file_name'
| MASTER_SSL_CIPHER = 'cipher_list'
| MASTER_SSL_VERIFY_SERVER_CERT = {0|1}
| IGNORE_SERVER_IDS = (server_id_list)
server_id_list:
[server_id [, server_id] ... ]
CHANGE MASTER TO changes the parameters that the slave server uses for
connecting to the master server, for reading the master binary log, and
reading the slave relay log. It also updates the contents of the
master.info and relay-log.info files. To use CHANGE MASTER TO, the
slave replication threads must be stopped (use STOP SLAVE if
necessary).
Options not specified retain their value, except as indicated in the
following discussion. Thus, in most cases, there is no need to specify
options that do not change. For example, if the password to connect to
your MySQL master has changed, you just need to issue these statements
to tell the slave about the new password:
STOP SLAVE; -- if replication was running
CHANGE MASTER TO MASTER_PASSWORD='new3cret';
START SLAVE; -- if you want to restart replication
MASTER_HOST, MASTER_USER, MASTER_PASSWORD, and MASTER_PORT provide
information to the slave about how to connect to its master:
o MASTER_HOST and MASTER_PORT are the host name (or IP address) of the
master host and its TCP/IP port.
*Note*: Replication cannot use Unix socket files. You must be able to
connect to the master MySQL server using TCP/IP.
If you specify the MASTER_HOST or MASTER_PORT option, the slave
assumes that the master server is different from before (even if the
option value is the same as its current value.) In this case, the old
values for the master binary log file name and position are
considered no longer applicable, so if you do not specify
MASTER_LOG_FILE and MASTER_LOG_POS in the statement,
MASTER_LOG_FILE='' and MASTER_LOG_POS=4 are silently appended to it.
Setting MASTER_HOST='' (that is, setting its value explicitly to an
empty string) is not the same as not setting MASTER_HOST at all.
Beginning with MySQL 5.5, trying to set MASTER_HOST to an empty
string fails with an error. Previously, setting MASTER_HOST to an
empty string caused START SLAVE subsequently to fail. (Bug #28796)
o MASTER_USER and MASTER_PASSWORD are the user name and password of the
account to use for connecting to the master.
In MySQL 5.5.20 and later, MASTER_USER cannot be made empty; setting
MASTER_USER = '' or leaving it unset when setting a value for for
MASTER_PASSWORD causes an error (Bug #13427949).
Currently, a password used for a replication slave account is
effectively limited to 32 characters in length; the password can be
longer, but any excess characters are truncated. This is not due to
any limit imposed by the MySQL Server generally, but rather is an
issue specific to MySQL Replication. (For more information, see Bug
#43439.)
The text of a running CHANGE MASTER TO statement, including values
for MASTER_USER and MASTER_PASSWORD, can be seen in the output of a
concurrent SHOW PROCESSLIST statement.
The MASTER_SSL_xxx options provide information about using SSL for the
connection. They correspond to the --ssl-xxx options described in
http://dev.mysql.com/doc/refman/5.5/en/ssl-options.html, and
http://dev.mysql.com/doc/refman/5.5/en/replication-solutions-ssl.html.
These options can be changed even on slaves that are compiled without
SSL support. They are saved to the master.info file, but are ignored if
the slave does not have SSL support enabled.
MASTER_CONNECT_RETRY specifies how many seconds to wait between connect
retries. The default is 60. The number of reconnection attempts is
limited by the --master-retry-count server option; for more
information, see
http://dev.mysql.com/doc/refman/5.5/en/replication-options.html.
The MASTER_BIND option is available in MySQL Cluster NDB 7.2 and later,
but is not supported in mainline MySQL 5.5.
MASTER_BIND is for use on replication slaves having multiple network
interfaces, and determines which of the slave's network interfaces is
chosen for connecting to the master.
MASTER_HEARTBEAT_PERIOD sets the interval in seconds between
replication heartbeats. Whenever the master's binary log is updated
with an event, the waiting period for the next heartbeat is reset.
interval is a decimal value having the range 0 to 4294967 seconds and a
resolution in milliseconds; the smallest nonzero value is 0.001.
Heartbeats are sent by the master only if there are no unsent events in
the binary log file for a period longer than interval.
Setting interval to 0 disables heartbeats altogether. The default value
for interval is equal to the value of slave_net_timeout divided by 2.
Setting @@global.slave_net_timeout to a value less than that of the
current heartbeat interval results in a warning being issued. The
effect of issuing RESET SLAVE on the heartbeat interval is to reset it
to the default value.
MASTER_LOG_FILE and MASTER_LOG_POS are the coordinates at which the
slave I/O thread should begin reading from the master the next time the
thread starts. RELAY_LOG_FILE and RELAY_LOG_POS are the coordinates at
which the slave SQL thread should begin reading from the relay log the
next time the thread starts. If you specify either of MASTER_LOG_FILE
or MASTER_LOG_POS, you cannot specify RELAY_LOG_FILE or RELAY_LOG_POS.
If neither of MASTER_LOG_FILE or MASTER_LOG_POS is specified, the slave
uses the last coordinates of the slave SQL thread before CHANGE MASTER
TO was issued. This ensures that there is no discontinuity in
replication, even if the slave SQL thread was late compared to the
slave I/O thread, when you merely want to change, say, the password to
use.
CHANGE MASTER TO deletes all relay log files and starts a new one,
unless you specify RELAY_LOG_FILE or RELAY_LOG_POS. In that case, relay
log files are kept; the relay_log_purge global variable is set silently
to 0.
Prior to MySQL 5.5, RELAY_LOG_FILE required an absolute path. In MySQL
5.5, the path can be relative, in which case the path is assumed to be
relative to the slave's data directory. (Bug #12190)
IGNORE_SERVER_IDS was added in MySQL 5.5. This option takes a
comma-separated list of 0 or more server IDs. Events originating from
the corresponding servers are ignored, with the exception of log
rotation and deletion events, which are still recorded in the relay
log.
In circular replication, the originating server normally acts as the
terminator of its own events, so that they are not applied more than
once. Thus, this option is useful in circular replication when one of
the servers in the circle is removed. Suppose that you have a circular
replication setup with 4 servers, having server IDs 1, 2, 3, and 4, and
server 3 fails. When bridging the gap by starting replication from
server 2 to server 4, you can include IGNORE_SERVER_IDS = (3) in the
CHANGE MASTER TO statement that you issue on server 4 to tell it to use
server 2 as its master instead of server 3. Doing so causes it to
ignore and not to propagate any statements that originated with the
server that is no longer in use.
If a CHANGE MASTER TO statement is issued without any IGNORE_SERVER_IDS
option, any existing list is preserved; RESET SLAVE also has no effect
on the server ID list. To clear the list of ignored servers, it is
necessary to use the option with an empty list:
CHANGE MASTER TO IGNORE_SERVER_IDS = ();
If IGNORE_SERVER_IDS contains the server's own ID and the server was
started with the --replicate-same-server-id option enabled, an error
results.
Also beginning with MySQL 5.5, the master.info file and the output of
SHOW SLAVE STATUS are extended to provide the list of servers that are
currently ignored. For more information, see
http://dev.mysql.com/doc/refman/5.5/en/slave-logs-status.html, and
[HELP SHOW SLAVE STATUS].
Beginning with MySQL 5.5.5, invoking CHANGE MASTER TO causes the
previous values for MASTER_HOST, MASTER_PORT, MASTER_LOG_FILE, and
MASTER_LOG_POS to be written to the error log, along with other
information about the slave's state prior to execution.
CHANGE MASTER TO is useful for setting up a slave when you have the
snapshot of the master and have recorded the master binary log
coordinates corresponding to the time of the snapshot. After loading
the snapshot into the slave to synchronize it to the slave, you can run
CHANGE MASTER TO MASTER_LOG_FILE='log_name', MASTER_LOG_POS=log_pos on
the slave to specify the coordinates at which the slave should begin
reading the master binary log.
The following example changes the master server the slave uses and
establishes the master binary log coordinates from which the slave
begins reading. This is used when you want to set up the slave to
replicate the master:
CHANGE MASTER TO
MASTER_HOST='master2.mycompany.com',
MASTER_USER='replication',
MASTER_PASSWORD='bigs3cret',
MASTER_PORT=3306,
MASTER_LOG_FILE='master2-bin.001',
MASTER_LOG_POS=4,
MASTER_CONNECT_RETRY=10;
The next example shows an operation that is less frequently employed.
It is used when the slave has relay log files that you want it to
execute again for some reason. To do this, the master need not be
reachable. You need only use CHANGE MASTER TO and start the SQL thread
(START SLAVE SQL_THREAD):
CHANGE MASTER TO
RELAY_LOG_FILE='slave-relay-bin.006',
RELAY_LOG_POS=4025;
URL: http://dev.mysql.com/doc/refman/5.5/en/change-master-to.html
mysql> CHANGE MASTER TO MASTER_HOST='172.16.100.6',MASTER_USER='repluser',MASTER_PASSWORD='replpass',MASTER_LOG_FILE='master-bin.000001',
MASTER_LOG_POS=656;（从主mysql服务器使用repluser用户密码replpass开始复制二进制文件，从656开始位置开始）
Query OK, 0 rows affected (0.11 sec)
mysql> SHOW SLAVE STATUS\G（查看从mysql状态）
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 172.16.100.6（主服务器地址）
Master_User: repluser（主服务器用户）
Master_Port: 3306（主服务器端口）
Connect_Retry: 60（如果连不上去多长事件重试一次）
Master_Log_File: master-bin.000001（当前读取的二进制日志文件）
Read_Master_Log_Pos: 656
Relay_Log_File: relay-log.000001
Relay_Log_Pos: 4（当前从服务器中继日志所处位置）
Relay_Master_Log_File: master-bin.000001（中继的主mysql服务器的日志文件）
Slave_IO_Running: No（slave io没有运行起来，如果从服务器正常为yes）
Slave_SQL_Running: No（slave sql也没有运行起来，如果从服务器正常为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: 656（本地执行的位置）
Relay_Log_Space: 107（中继日志空间）
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No（master主从服务是否允许使用ssl）
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL（从服务器要比主服务器慢多少）
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0（本地io信息）
Last_IO_Error:
Last_SQL_Errno: 0（本地线程信息）
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
1 row in set (0.00 sec)
mysql> START SLAVE;（启动备用服务器）
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW SLAVE STATUS\G（查看从服务器状态）
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.100.6
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 656
Relay_Log_File: relay-log.000002
Relay_Log_Pos: 254
Relay_Master_Log_File: master-bin.000001
Slave_IO_Running: Yes（salve io启动）
Slave_SQL_Running: Yes（slave 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:
Skip_Counter: 0
Exec_Master_Log_Pos: 656
Relay_Log_Space: 404
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
1 row in set (0.00 sec)
node1:
mysql> SHOW DATABASES; （显示数据库） +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+ 4 rows in set (0.00 sec) mysql> CREATE DATABASE magedudb;（创建数据库magedudb） Query OK, 1 row affected (0.03 sec)
node2:
mysql> SHOW SLAVE STATUS\G（查看从服务器状态）
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.100.6
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 747（log pos更改）
Relay_Log_File: relay-log.000002
Relay_Log_Pos: 345
Relay_Master_Log_File: master-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: 747（事件已经复制过来在本地应用了，本地应该有这个数据库了）
Relay_Log_Space: 495
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
1 row in set (0.00 sec)
mysql> SHOW DATABASES;（显示数据库）
+--------------------+
| Database |
+--------------------+
| information_schema |
| magedudb |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.01 sec)
提示：在从服务器创建，主服务器是不会有的，所以从服务器是一定不允许写操作的；
mysql> SHOW GLOBAL VARIABLES LIKE 'read%';（查找全局编辑包含read相关）
+----------------------+---------+
| Variable_name | Value |
+----------------------+---------+
| read_buffer_size | 1048576 |
| read_only | OFF |（只允许只读，不允许网数据库写数据）
| read_rnd_buffer_size | 4194304 |
+----------------------+---------+
3 rows in set (0.00 sec)
提示：又有管理员才能修改全局变量，在这里修改退出后会失效，最好是修改配置文件；
mysql> \q
Bye
[root@node2 mysql]# vim /etc/my.cnf（编辑my.cnf配置文件）
read-only = ON（启动mysql只读）
[root@node2 mysql]# service mysqld restart（重启mysqld服务）
Shutting down MySQL. [确定]
Starting MySQL.. [确定]
[root@node2 mysql]# mysql（连接mysql）
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.28 MySQL Community Server (GPL)
Copyright (c) 2000, 2012, 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 GLOBAL VARIABLES LIKE 'read_only';（查看mysql全局变量read_only相关）
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only | ON |
+---------------+-------+
1 row in set (0.00 sec)
提示：read_only为ON；
mysql> SHOW SLAVE STATUS\G（查看备用mysql状态）
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.100.6
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 747
Relay_Log_File: relay-log.000004
Relay_Log_Pos: 254
Relay_Master_Log_File: master-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: 747
Relay_Log_Space: 404
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
1 row in set (0.00 sec)
mysql> \q（退出）
Bye
[root@node2 mysql]# cd /mydata/data/（切换到/mydata/data目录）
[root@node2 data]# ls（查看当前目录文件及子目录）
ibdata1 ib_logfile1 master-bin.000001 master-bin.index mysql node2.magedu.com.pid relay-log.000003 relay-log.index test
ib_logfile0 magedudb master-bin.000002 master.info node2.magedu.com.err performance_schema relay-log.000004 relay-log.info
[root@node2 data]# file master.info（查看master.info文件类型）
master.info: ASCII text
[root@node2 data]# cat master.info（查看master.info文件）
18
master-bin.000001（现在读的是那个文件）
747（位置）
172.16.100.6（主服务器地址）
repluser（帐号）
replpass（密码）
3306（端口）
60（重试时间)
0
0
1800.000
[root@node2 data]# ll（查看当前目录文件及子目录详细信息）
总计 29888
-rw-rw---- 1 mysql mysql 18874368 06-19 02:22 ibdata1
-rw-rw---- 1 mysql mysql 5242880 06-19 02:23 ib_logfile0
-rw-rw---- 1 mysql mysql 5242880 06-19 01:14 ib_logfile1
drwx------ 2 mysql mysql 4096 06-19 02:04 magedudb
-rw-rw---- 1 mysql mysql 27323 06-19 01:09 master-bin.000001
-rw-rw---- 1 mysql mysql 1038693 06-19 01:09 master-bin.000002
-rw-rw---- 1 mysql mysql 40 06-19 01:09 master-bin.index
-rw-rw---- 1 mysql mysql 85 06-19 02:23 master.info
drwx------ 2 mysql root 4096 06-19 01:09 mysql
-rw-rw---- 1 mysql root 4861 06-19 02:23 node2.magedu.com.err
-rw-rw---- 1 mysql mysql 6 06-19 02:23 node2.magedu.com.pid
drwx------ 2 mysql mysql 4096 06-19 01:09 performance_schema
-rw-rw---- 1 mysql mysql 150 06-19 02:23 relay-log.000003
-rw-rw---- 1 mysql mysql 254 06-19 02:23 relay-log.000004
-rw-rw---- 1 mysql mysql 38 06-19 02:23 relay-log.index
-rw-rw---- 1 mysql mysql 45 06-19 02:23 relay-log.info
drwx------ 2 mysql root 4096 06-19 01:09 test
[root@node2 data]# file relay-log.info（查看relay-log.info文件类型）
relay-log.info: ASCII text
[root@node2 data]# cat relay-log.info（查看relay-log.info文件内容）
./relay-log.000004（当前使用的中继日志文件）
254（事件位置）
master-bin.000001（正在读取的主服务器的二进制日志文件）
747（事件位置）
提示：master.info和relay-log.info是实现复制的前提；
node1:
[root@node1 ~]# mysql（连接mysql数据库） Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 6 Server version: 5.5.28-log MySQL Community Server (GPL) Copyright (c) 2000, 2012, 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. [root@node1 ~]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 6 Server version: 5.5.28-log MySQL Community Server (GPL) Copyright (c) 2000, 2012, 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 GLOBAL VARIABLES LIKE '%log%';（查看mysql全局变量和log相关的参数） +-----------------------------------------+-----------------------------------+ | Variable_name | Value | +-----------------------------------------+-----------------------------------+ | back_log | 50 | | binlog_cache_size | 32768 | | binlog_direct_non_transactional_updates | OFF | | binlog_format | MIXED | | binlog_stmt_cache_size | 32768 | | expire_logs_days | 0 | | general_log | OFF | | general_log_file | /mydata/data/node1.log | | innodb_flush_log_at_trx_commit | 1 | | innodb_locks_unsafe_for_binlog | OFF | | innodb_log_buffer_size | 8388608 | | innodb_log_file_size | 5242880 | | innodb_log_files_in_group | 2 | | innodb_log_group_home_dir | ./ | | innodb_mirrored_log_groups | 1 | | log | OFF | | log_bin | ON | | log_bin_trust_function_creators | OFF | | log_error | /mydata/data/node1.magedu.com.err | | log_output | FILE | | log_queries_not_using_indexes | OFF | | log_slave_updates | OFF | | log_slow_queries | OFF | | log_warnings | 1 | | max_binlog_cache_size | 18446744073709547520 | | max_binlog_size | 1073741824 | | max_binlog_stmt_cache_size | 18446744073709547520 | | max_relay_log_size | 0 | | relay_log | | | relay_log_index | | | relay_log_info_file | relay-log.info | | relay_log_purge | ON | | relay_log_recovery | OFF | | relay_log_space_limit | 0 | | slow_query_log | OFF | | slow_query_log_file | /mydata/data/node1-slow.log | | sql_log_bin | ON | | sql_log_off | OFF | | sync_binlog | 0 |（是不是同步二进制日志，只要在主服务器尽可能的事物安全，在主从复制架构中， 这个应该启用起来的） | sync_relay_log | 0 | | sync_relay_log_info | 0 | +-----------------------------------------+-----------------------------------+ 41 rows in set (0.00 sec)
node2:
[root@node2 data]# mysql（连接mysql数据库）
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.5.28 MySQL Community Server (GPL)
Copyright (c) 2000, 2012, 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 GLOBAL VARIABLES LIKE '%SLAVE%';（查看mysql全局变量和slave相关参数）
+---------------------------+------------+
| Variable_name | Value |
+---------------------------+------------+
| init_slave | |
| log_slave_updates | OFF |
| slave_compressed_protocol | OFF |
| slave_exec_mode | STRICT |
| slave_load_tmpdir | /tmp |
| slave_max_allowed_packet | 1073741824 |
| slave_net_timeout | 3600 |
| slave_skip_errors | OFF |
| slave_transaction_retries | 10 |
| slave_type_conversions | |
| sql_slave_skip_counter | 0 |
+---------------------------+------------+
11 rows in set (0.00 sec)
mysql> \q（退出）
Bye
[root@node2 data]# pwd（查看当前所处的路径）
/mydata/data
[root@node2 data]# ls（查看当前目录文件及子目录）
ibdata1 ib_logfile1 master-bin.000001 master-bin.index mysql node2.magedu.com.pid relay-log.000003 relay-log.index
test ib_logfile0 magedudb master-bin.000002 master.info node2.magedu.com.err performance_schema relay-log.000004
relay-log.info
[root@node2 data]# mysql（连接mysql数据库）
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.5.28 MySQL Community Server (GPL)
Copyright (c) 2000, 2012, 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> STOP SLAVE;（停止从服务器线程）
Query OK, 0 rows affected (0.05 sec)
mysql> SHOW SLAVE STATUS\G（查看mysql从服务器状态）
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 172.16.100.6
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 747
Relay_Log_File: relay-log.000004
Relay_Log_Pos: 254
Relay_Master_Log_File: master-bin.000001
Slave_IO_Running: No（slave io停止）
Slave_SQL_Running: No（slave 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:
Skip_Counter: 0
Exec_Master_Log_Pos: 747
Relay_Log_Space: 404
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: 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
1 row in set (0.00 sec)
mysql> START SLAVE IO_THREAD;（启动slave io线程）
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW SLAVE STATUS\G（查看mysql从服务器状态，\G树状显示）
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.100.6
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 747
Relay_Log_File: relay-log.000004
Relay_Log_Pos: 254
Relay_Master_Log_File: master-bin.000001
Slave_IO_Running: Yes（slave io线程启动）
Slave_SQL_Running: No
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: 747
Relay_Log_Space: 701
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: 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
1 row in set (0.00 sec)
mysql> START SLAVE SQL_THREAD;（启动从slave sql线程）
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW SLAVE STATUS\G（查看mysql从服务器状态）
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.100.6
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 747
Relay_Log_File: relay-log.000005
Relay_Log_Pos: 254
Relay_Master_Log_File: master-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes（slave 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:
Skip_Counter: 0
Exec_Master_Log_Pos: 747
Relay_Log_Space: 551
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
1 row in set (0.00 sec)
mysql> STOP SLAVE IO_THREAD;（停止slave io线程）
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW SLAVE STATUS\G（查看mysql从服务器状态）
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 172.16.100.6
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 747
Relay_Log_File: relay-log.000005
Relay_Log_Pos: 254
Relay_Master_Log_File: master-bin.000001
Slave_IO_Running: No（slave io 线程停止）
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: 747
Relay_Log_Space: 551
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: 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
1 row in set (0.00 sec)
mysql> START SLAVE IO_THREAD;（启动slave io线程）
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW SLAVE STATUS\G（查看mysql从服务器状态）
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.100.6
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 747
Relay_Log_File: relay-log.000006
Relay_Log_Pos: 254
Relay_Master_Log_File: master-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: 747
Relay_Log_Space: 551
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
1 row in set (0.00 sec)
mysql> \q（退出）
Bye
[root@node2 data]# tail /mydata/data/node2.magedu.com.err（查看node2.magedu.com.err日志文件后10行）
160619 4:47:49 [Note] Error reading relay log event: slave SQL thread was killed
160619 4:47:50 [ERROR] Error reading packet from server: Lost connection to MySQL server during query ( server_errno=2013)
160619 4:47:50 [Note] Slave I/O thread killed while reading event
160619 4:47:50 [Note] Slave I/O thread exiting, read up to log 'master-bin.000001', position 747
160619 4:49:30 [Note] Slave I/O thread: connected to master 'repluser@172.16.100.6:3306',replication started in log 'master-bin.000001' at p
osition 747
160619 4:50:51 [Note] Slave SQL thread initialized, starting replication in log 'master-bin.000001' at position 747, relay log './relay-log.
000004' position: 254
160619 4:52:07 [ERROR] Error reading packet from server: Lost connection to MySQL server during query ( server_errno=2013)
160619 4:52:07 [Note] Slave I/O thread killed while reading event
160619 4:52:07 [Note] Slave I/O thread exiting, read up to log 'master-bin.000001', position 747
160619 4:54:13 [Note] Slave I/O thread: connected to master 'repluser@172.16.100.6:3306',replication started in log 'master-bin.000001' at p
osition 747
提示：所有跟从服务器启动相关的内容都被保存在/mydata/data/node2.magedu.com.err日志文件；
[root@node2 data]# cd（切换到用户家目录）
[root@node2 ~]# cd /usr/local/mysql（切换到/usr/local/mysql目录）
[root@node2 mysql]# ls（查看当前目录文件及子目录）
bin COPYING data docs include INSTALL-BINARY lib man mysql-test README scripts share sql-bench support-files
[root@node2 mysql]# cd lib/plugin/（切换到lib/plugin目录）
[root@node2 plugin]# ls（查看当前目录文件及子目录）
adt_null.so auth_socket.so daemon_example.ini libdaemon_example.so qa_auth_client.so qa_auth_server.so semisync_slave.so（半同步
复制从服务器安装插件）
auth.so auth_test_plugin.so debug mypluglib.so qa_auth_interface.so semisync_master.so（半同步复制主服务器安装的插件）
node1:
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';（安装mysql主服务器半同步插件） Query OK, 0 rows affected (0.05 sec) mysql> SHOW GLOBAL VARIABLES LIKE '%rpl%';（查看全局变量rpl相关） +------------------------------------+-------+ | Variable_name | Value | +------------------------------------+-------+ | rpl_recovery_rank | 0 | | rpl_semi_sync_master_enabled | OFF |（模块装进来但是没有启用，是否启用半同步复制的主节点） | rpl_semi_sync_master_timeout | 10000 |（超时时间） | rpl_semi_sync_master_trace_level | 32 |（追踪级别） | rpl_semi_sync_master_wait_no_slave | ON |（如果没有slave我们要不要等待） +------------------------------------+-------+ 5 rows in set (0.00 sec)
node2:
[root@node2 plugin]# cd（切换到用户家目录） [root@node2 ~]# mysql（连接mysql数据库） Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 10 Server version: 5.5.28 MySQL Community Server (GPL) Copyright (c) 2000, 2012, 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> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';（安装mysql从服务器半同步插件） Query OK, 0 rows affected (0.00 sec) mysql> SHOW VARIABLES LIKE 'rpl%';（查看全局变量rpl相关） +---------------------------------+-------+ | Variable_name | Value | +---------------------------------+-------+ | rpl_recovery_rank | 0 | | rpl_semi_sync_slave_enabled | OFF |（半同步没有启动） | rpl_semi_sync_slave_trace_level | 32 | +---------------------------------+-------+ 3 rows in set (0.00 sec)
node1:
mysql> SET GLOBAL rpl_semi_sync_master_enabled=1;（启用主mysql全局变量半同步参数） Query OK, 0 rows affected (0.00 sec) mysql> SHOW GLOBAL VARIABLES LIKE '%rpl%';（查看全局变量rpl相关的参数） +------------------------------------+-------+ | Variable_name | Value | +------------------------------------+-------+ | rpl_recovery_rank | 0 | | rpl_semi_sync_master_enabled | ON |（master半同步已经启动） | rpl_semi_sync_master_timeout | 10000 |（等待超时时间） | rpl_semi_sync_master_trace_level | 32 | | rpl_semi_sync_master_wait_no_slave | ON | +------------------------------------+-------+ 5 rows in set (0.00 sec)
node2:
mysql> SET GLOBAL rpl_semi_sync_slave_enabled=1;（启用全局变量备用mysql半同步功能） Query OK, 0 rows affected (0.00 sec) mysql> SHOW VARIABLES LIKE 'rpl%';（查看全局变量rpl相关） +---------------------------------+-------+ | Variable_name | Value | +---------------------------------+-------+ | rpl_recovery_rank | 0 | | rpl_semi_sync_slave_enabled | ON |（半同步已经启动） | rpl_semi_sync_slave_trace_level | 32 | +---------------------------------+-------+ 3 rows in set (0.00 sec)
node1:
mysql> SHOW GLOBAL STATUS LIKE 'rpl%';（查看全局状态rpl相关参数） +--------------------------------------------+-------------+ | Variable_name | Value | +--------------------------------------------+-------------+ | Rpl_semi_sync_master_clients | 0 |（半同步客户端有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 | | 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_status | AUTH_MASTER | +--------------------------------------------+-------------+ 15 rows in set (0.00 sec)
node2:
mysql> STOP SLAVE IO_THREAD;（停止slave to线程） Query OK, 0 rows affected (0.03 sec) mysql> START SLAVE IO_THREAD;（启动slave to线程） Query OK, 0 rows affected (0.00 sec)
node1:
mysql> SHOW GLOBAL STATUS LIKE 'rpl%';（查看全局状态rpl相关） +--------------------------------------------+-------------+ | Variable_name | Value | +--------------------------------------------+-------------+ | Rpl_semi_sync_master_clients | 1 |（半同步客户端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 | | Rpl_status | AUTH_MASTER | +--------------------------------------------+-------------+ 15 rows in set (0.00 sec) mysql> USE magedudb;（修改默认数据库为magedudb） Database changed mysql> CREATE TABLE tb1 (id int);（创建表tb1，字段id，整型） Query OK, 0 rows affected (0.05 sec) 提示：使用半同步模式创建表时间很长，如果没有使用半同步这个表创建瞬间完成，这个必须要依赖网络通信才能结束的；
node2:
mysql> STOP SLAVE IO_THREAD;（停止slave io线程） Query OK, 0 rows affected (0.01 sec)
node1:
mysql> CREATE TABLE tb2 (id int);（创建tb2表，字段id，整型） Query OK, 0 rows affected (10.04 sec) 提示：卡住事件10.04s，超时时间，如果定义为1s要等待1秒，定义10s等待10s； mysql> SHOW GLOBAL STATUS LIKE 'rpl%';（查看全局状态rpl相关） +--------------------------------------------+-------------+ | Variable_name | Value | +--------------------------------------------+-------------+ | Rpl_semi_sync_master_clients | 1 | | Rpl_semi_sync_master_net_avg_wait_time | 849 |（网络等待的平均时间） | Rpl_semi_sync_master_net_wait_time | 1699 |（这次时间） | 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 | OFF | | Rpl_semi_sync_master_timefunc_failures | 0 | | Rpl_semi_sync_master_tx_avg_wait_time | 1598 | | Rpl_semi_sync_master_tx_wait_time | 1598 | | 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_status | AUTH_MASTER | +--------------------------------------------+-------------+ 15 rows in set (0.00 sec) mysql> SHOW GLOBAL STATUS LIKE 'rpl%';（查看全局状态rpl相关） +--------------------------------------------+-------------+ | Variable_name | Value | +--------------------------------------------+-------------+ | Rpl_semi_sync_master_clients | 1 | | Rpl_semi_sync_master_net_avg_wait_time | 849 | | Rpl_semi_sync_master_net_wait_time | 1699 | | 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 | OFF | | Rpl_semi_sync_master_timefunc_failures | 0 | | Rpl_semi_sync_master_tx_avg_wait_time | 1598 | | Rpl_semi_sync_master_tx_wait_time | 1598 | | 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_status | AUTH_MASTER | +--------------------------------------------+-------------+ 15 rows in set (0.00 sec) 提示：一般等待超时会自动降级为半同步； mysql> CREATE TABLE tb3 (id int);（创建表tb3，字段id，整型） Query OK, 0 rows affected (0.06 sec) 提示：这次速度很快了，所以一旦有一个超时了，自动降级为异步；
node2:
mysql> START SLAVE IO_THREAD;（启动slave io线程）
Query OK, 0 rows affected (0.00 sec)
提示：重新启动以后他会重新跟上去同步的；
mysql> SHOW SLAVE STATUS\G（查看从mysql状态）
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.100.6
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 1023（当前复制的master log pos位置1023）
Relay_Log_File: relay-log.000008
Relay_Log_Pos: 438
Relay_Master_Log_File: master-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: 1023
Relay_Log_Space: 827
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
1 row in set (0.00 sec)
node1:
mysql> SHOW MASTER STATUS;（查看主mysql服务器状态） +-------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +-------------------+----------+--------------+------------------+ | master-bin.000001 | 1023 | | | +-------------------+----------+--------------+------------------+ 1 row in set (0.00 sec)
node2:
mysql> SHOW SLAVE STATUS\G（查看从mysql状态）
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.100.6
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 1023
Relay_Log_File: relay-log.000008
Relay_Log_Pos: 438
Relay_Master_Log_File: master-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: 1023
Relay_Log_Space: 827
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
1 row in set (0.00 sec)
mysql> \q（退出）
Bye
[root@node2 ~]# lftp 172.16.0.1（连接ftp服务器）
lftp 172.16.0.1:~> cd pub/Sources/percona/（切换到pub/Sources/percora目录）
lftp 172.16.0.1:/pub/Sources/percona> cd toolkit/
lftp 172.16.0.1:/pub/Sources/percona/toolkit> get percona-toolkit-2.2.2-1.noarch.rpm（下载percona-toolkit-2.2.2）
1671248 bytes transferred
lftp 172.16.0.1:/pub/Sources/percona/toolkit> bye（退出）
[root@node2 ~]# ls（查看当前目录文件及子目录）
anaconda-ks.cfg install.log install.log.syslog mysql-5.5.28-linux2.6-i686.tar.gz percona-toolkit-2.2.2-1.noarch.rpm
[root@node2 ~]# rpm -ivh percona-toolkit-2.2.2-1.noarch.rpm（安装percona-toolkit的rpm包，-i安装，-v显示安装过程，-h显示进度条）
warning: percona-toolkit-2.2.2-1.noarch.rpm: Header V4 DSA signature: NOKEY, key ID cd2efd2a
error: Failed dependencies:
perl(DBI) >= 1.13 is needed by percona-toolkit-2.2.2-1.noarch
perl(DBD::mysql) >= 1.0 is needed by percona-toolkit-2.2.2-1.noarch
perl(IO::Socket::SSL) is needed by percona-toolkit-2.2.2-1.noarch
提示：这种包依赖于mysql库；
[root@node2 ~]# yum -y localinstall --nogpgcheck percona-toolkit-2.2.2-1.noarch.rpm（安装本地rpm软件包，-y所有询问回答yes，--nogpgcheck不做gpg校验）
[root@node2 ~]# vim /etc/my.cnf（编辑my.cnf配置文件）
提示：percona-toolkit安装完以后它也会创建my.cnf配置文件，请确保它没有改我们的配置文件；
[root@node2 ~]# pt（查看pt开头命令）
pt-align pt-fifo-split pt-ioprofile pt-query-digest pt-stalk
pt-variable-advisor pt-archiver pt-find pt-kill pt-show-grants
pt-summary（收集服务器信息cpu类型、内存大小等等） pt-visual-explain（可视化explain） pt-config-diff
pt-fingerprint pt-mext pt-sift pt-table-checksum（跟复制相关的） ptx
pt-deadlock-logger pt-fk-error-logger pt-mysql-summary pt-slave-delay pt-table-sync
pt-diskstats（显示磁盘io的相关统计数据的） pt-heartbeat pt-online-schema-change pt-slave-find
pt-table-usage pt-duplicate-key-checker pt-index-usage（显示当前索引使用情况） pt-pmp
pt-slave-restart pt-upgrade
[root@node2 ~]# pt-slave-delay -h（查看pt-slave-delay的命令帮助，这个命令专门用户从服务器比主服务器慢一段时间）
Option h requires an argument
Usage: pt-slave-delay [OPTIONS] SLAVE_DSN [MASTER_DSN]
Errors in command-line arguments:
* Error parsing options
* Missing or invalid slave host
pt-slave-delay starts and stops a slave server as needed to make it lag behind
the master. The SLAVE_DSN and MASTER_DSN use DSN syntax, and values are copied
from the SLAVE_DSN to the MASTER_DSN if omitted. For more details, please use
the --help option, or try 'perldoc /usr/bin/pt-slave-delay' for complete
documentation.（有意的让从服务器比主机服务器慢一段时间，这样在主服务器做的一些误操作可以有时间去挽救）
[root@node2 ~]# pt-table-checksum --help（查看pt-table-checksum的命令帮助）
pt-table-checksum performs an online replication consistency check by executing
checksum queries on the master, which produces different results on replicas
that are inconsistent with the master. The optional DSN specifies the master
host. The tool's exit status is nonzero if any differences are found, or if any
warnings or errors occur. For more details, please use the --help option, or
try 'perldoc /usr/bin/pt-table-checksum' for complete documentation.（执行一个在线的复制一致性检查，它能够在从服务器上检查从服务器的表跟主服务器的表俩数
据是否是一致的）
Usage: pt-table-checksum [OPTIONS] [DSN]（制定主服务器是谁）
Options:
--[no]check-binlog-format Check that the binlog_format is the same on
all servers (default yes)
--[no]check-plan Check query execution plans for safety (
default yes)
--chunk-index=s Prefer this index for chunking tables
--chunk-index-columns=i Use only this many left-most columns of a --
chunk-index
--chunk-size=z Number of rows to select for each checksum
query (default 1000)
--chunk-time=f Adjust the chunk size dynamically so each
checksum query takes this long to execute (
default 0.5)
--[no]create-replicate-table Create the --replicate database and table if
they do not exist (default yes)
--[no]empty-replicate-table Delete previous checksums for each table
before checksumming the table (default yes)
--float-precision=i Precision for FLOAT and DOUBLE number-to-
string conversion
--function=s Hash function for checksums (FNV1A_64,
MURMUR_HASH, SHA1, MD5, CRC32, etc)
--pid=s Create the given PID file
--progress=a Print progress reports to STDERR (default
time,30)
--quiet -q Print only the most important information (
disables --progress) (default 0)
--recurse=i Number of levels to recurse in the hierarchy
when discovering replicas
--recursion-method=a Preferred recursion method for discovering
replicas. pt-table-checksum performs several
REPLICA CHECKS before and while running (
default processlist,hosts)
--replicate=s Write checksum results to this table (
default percona.checksums)
--[no]replicate-check Check replicas for data differences after
finishing each table (default yes)
--replicate-check-only Check replicas for consistency without
executing checksum queries
--replicate-database=s USE only this database
--resume Resume checksumming from the last completed
chunk (disables --[no]empty-replicate-table)
--retries=i Retry a chunk this many times when there is
a nonfatal error (default 2)
--run-time=m How long to run. Optional suffix s=seconds,
m=minutes, h=hours, d=days; if no suffix, s
is used.
--separator=s The separator character used for
CONCAT_WS() (default #)
--trim Add TRIM() to VARCHAR columns (helps when
comparing 4.1 to >= 5.0)
--[no]version-check Check for the latest version of Percona
Toolkit, MySQL, and other programs (default
yes)
--where=s Do only rows matching this WHERE clause
Config:
--config=A Read this comma-separated list of config
files; if specified, this must be the first
option on the command line
Connection:
--ask-pass Prompt for a password when connecting to
MySQL
--defaults-file=s -F Only read mysql options from the given file
--host=s -h Host to connect to (default localhost)
--password=s -p Password to use when connecting
--port=i -P Port number to use for connection
--set-vars=A Set the MySQL variables in this comma-
separated list of variable=value pairs
--socket=s -S Socket file to use for connection
--user=s -u User for login if not current user
Filter:
--columns=a -c Checksum only this comma-separated list of
columns
--databases=h -d Only checksum this comma-separated list of
databases
--databases-regex=s Only checksum databases whose names match
this Perl regex
--engines=h -e Only checksum tables which use these storage
engines
--ignore-columns=H Ignore this comma-separated list of columns
when calculating the checksum
--ignore-databases=H Ignore this comma-separated list of databases
--ignore-databases-regex=s Ignore databases whose names match this Perl
regex
--ignore-engines=H Ignore this comma-separated list of storage
engines (default FEDERATED,MRG_MyISAM)
--ignore-tables=H Ignore this comma-separated list of tables
--ignore-tables-regex=s Ignore tables whose names match the Perl
regex
--tables=h -t Checksum only this comma-separated list of
tables
--tables-regex=s Checksum only tables whose names match this
Perl regex
Help:
--help Show help and exit
--version Show version and exit
Output:
--explain Show, but do not execute, checksum queries (
disables --[no]empty-replicate-table) (
default 0)
Safety:
--[no]check-replication-filters Do not checksum if any replication filters
are set on any replicas (default yes)
--[no]check-slave-tables Checks that tables on slaves exist and have
all the checksum --columns (default yes)
--chunk-size-limit=f Do not checksum chunks this much larger than
the desired chunk size (default 2.0)
Throttle:
--check-interval=m Sleep time between checks for --max-lag (
default 1). Optional suffix s=seconds, m=
minutes, h=hours, d=days; if no suffix, s is
used.
--check-slave-lag=s Pause checksumming until this replica's lag
is less than --max-lag
--max-lag=m Pause checksumming until all replicas' lag
is less than this value (default 1s).
Optional suffix s=seconds, m=minutes, h=
hours, d=days; if no suffix, s is used.
--max-load=A Examine SHOW GLOBAL STATUS after every
chunk, and pause if any status variables are
higher than the threshold (default
Threads_running=25)
Option types: s=string, i=integer, f=float, h/H/a/A=comma-separated list, d=DSN, z=size, m=time
Rules:
This tool accepts additional command-line arguments. Refer to the SYNOPSIS and usage information for details.
DSN syntax is key=value[,key=value...] Allowable DSN keys:
KEY COPY MEANING
=== ==== =============================================
A yes Default character set
D no DSN table database
F yes Defaults file for connection values
P yes Port number to use for connection
S no Socket file to use for connection
h yes Connect to host
p yes Password to use when connecting
t no DSN table table
u yes User for login if not current user
If the DSN is a bareword, the word is treated as the 'h' key.
Options and values after processing arguments:
--ask-pass FALSE
--check-binlog-format TRUE
--check-interval 1
--check-plan TRUE
--check-replication-filters TRUE
--check-slave-lag (No value)
--check-slave-tables TRUE
--chunk-index (No value)
--chunk-index-columns (No value)
--chunk-size 1000
--chunk-size-limit 2.0
--chunk-time 0.5
--columns (No value)
--config /etc/percona-toolkit/percona-toolkit.conf,/etc/percona-toolkit/pt-table-checksum.conf,/root/.percona-
toolkit.conf,/root/.pt-table-checksum.conf
--create-replicate-table TRUE
--databases (No value)
--databases-regex (No value)
--defaults-file (No value)
--empty-replicate-table TRUE
--engines (No value)
--explain 0
--float-precision (No value)
--function (No value)
--help TRUE
--host localhost
--ignore-columns
--ignore-databases
--ignore-databases-regex (No value)
--ignore-engines FEDERATED,MRG_MyISAM
--ignore-tables percona.checksums
--ignore-tables-regex (No value)
--max-lag 1
--max-load Threads_running=25
--password (No value)
--pid (No value)
--port (No value)
--progress time,30
--quiet 0
--recurse (No value)
--recursion-method processlist,hosts
--replicate percona.checksums
--replicate-check TRUE
--replicate-check-only FALSE
--replicate-database (No value)
--resume FALSE
--retries 2
--run-time (No value)
--separator #
--set-vars
--socket (No value)
--tables (No value)
--tables-regex (No value)
--trim FALSE
--user (No value)
--version FALSE
--version-check TRUE
--where (No value)
[root@node2 ~]# pt（查看pt开头的相关命令）
pt-align pt-fifo-split pt-ioprofile（评估当前服务器io能力） pt-query-digest pt-stalk
pt-variable-advisor pt-archiver pt-find pt-kill pt-show-grants pt-summary
pt-visual-explain pt-config-diff pt-fingerprint pt-mext pt-sift
pt-table-checksum ptx pt-deadlock-logger pt-fk-error-logger pt-mysql-summary
pt-slave-delay pt-table-sync pt-diskstats pt-heartbeat pt-online-schema-change
pt-slave-find pt-table-usage pt-duplicate-key-checker pt-index-usage pt-pmp
pt-slave-restart pt-upgrade
node1:
mysql> SHOW GLOBAL VARIABLES LIKE '%ssl%';（查看全局变量和ssl相关参数）
+---------------+----------+
| Variable_name | Value |
+---------------+----------+
| have_openssl | DISABLED |
| have_ssl | DISABLED |（ssl关闭）
| ssl_ca | |
| ssl_capath | |
| ssl_cert | |（mysql服务器自己的证书）
| ssl_cipher | |（加密算法）
| ssl_key | |（私钥）
+---------------+----------+
7 rows in set (0.00 sec)
mysql> HELP GRANT;（查看GRANT命令帮助）
Name: 'GRANT'
Description:
Syntax:
GRANT
priv_type [(column_list)]
[, priv_type [(column_list)]] ...
ON [object_type] priv_level
TO user_specification [, user_specification] ...
[REQUIRE {NONE | ssl_option [[AND] ssl_option] ...}]
[WITH with_option ...]
GRANT PROXY ON user_specification
TO user_specification [, user_specification] ...
[WITH GRANT OPTION]
object_type:
TABLE
| FUNCTION
| PROCEDURE
priv_level:
*
| *.*
| db_name.*
| db_name.tbl_name
| tbl_name
| db_name.routine_name
user_specification:
user
[
IDENTIFIED BY [PASSWORD] 'password'
| IDENTIFIED WITH auth_plugin [AS 'auth_string']
]
ssl_option:
SSL
| X509
| CIPHER 'cipher'
| ISSUER 'issuer'
| SUBJECT 'subject'
with_option:
GRANT OPTION
| MAX_QUERIES_PER_HOUR count
| MAX_UPDATES_PER_HOUR count
| MAX_CONNECTIONS_PER_HOUR count
| MAX_USER_CONNECTIONS count
The GRANT statement grants privileges to MySQL user accounts. GRANT
also serves to specify other account characteristics such as use of
secure connections and limits on access to server resources. To use
GRANT, you must have the GRANT OPTION privilege, and you must have the
privileges that you are granting.
Normally, a database administrator first uses CREATE USER to create an
account, then GRANT to define its privileges and characteristics. For
example:
CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';
GRANT ALL ON db1.* TO 'jeffrey'@'localhost';
GRANT SELECT ON db2.invoice TO 'jeffrey'@'localhost';
GRANT USAGE ON *.* TO 'jeffrey'@'localhost' WITH MAX_QUERIES_PER_HOUR 90;
However, if an account named in a GRANT statement does not already
exist, GRANT may create it under the conditions described later in the
discussion of the NO_AUTO_CREATE_USER SQL mode.
The REVOKE statement is related to GRANT and enables administrators to
remove account privileges. See [HELP REVOKE].
When successfully executed from the mysql program, GRANT responds with
Query OK, 0 rows affected. To determine what privileges result from the
operation, use SHOW GRANTS. See [HELP SHOW GRANTS].
URL: http://dev.mysql.com/doc/refman/5.5/en/grant.html
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'172.116.%.%' IDENTIFIED BY 'replpass' REQUIRE SSL;（全授权'repluser'@'172.116.%.%'到主服务
器复制必须使用SSL，如果不使用不予以响应）
MySQL: 数据库复制过滤
主Mysql服务器端：
binlog-do-db=: 白名单，仅将指定数据库的相关修改操作记入二进制日志，会导致二进制日志不完整不利于数据恢复；
binlog-ignore-db=: 黑名单；
备Mysql服务器端:
replicate-do-db=: 应用那些数据库相关内容；
replicate-ignore-db=: 忽略那些数据库相关内容；
replicate-do-table=: 仅复制那些表；
replicate-ignore-table=: 忽略那些表；
replicate-wild-do-table=:
replicate-wild-ignore-table=:
数据库复制过滤通配符:
%: 任何长度任何字符
_: 一个字符；
MySQL-5.6: GTID
slave-parallel-workers=0（尽可能等于复制的数据库数）
0：标识禁用多线程功能
NetworkManager
network
/etc/udev/rules.d/70-persistent-net.rule
About GTID
GTIDs are unique identifiers comprising the server UUID(of the original master)and a transaction number（gtid是一个唯一的标识符，它是由服务器的uuid全局唯一标识和事物编号组成）
They are automatically generated as a header for every transction and written with the transaction to the binary log（在每一个事物的首部都会写上这个gtid的标识）
GTIDs make it simple to track and compare replicated transactions between the master and slaves,which in turn enables simple recovery from failures of the master（gtid使得追踪和比较复制事物变得非常简单，而且能够实现从崩溃中快速进行恢复）
The default InnoDB storage engine must be used with GTIDs to get the full benefits of HA（innodb存储引擎要想使用高可用功能必须要借助于gtid）
Global Transaction Identifiers(GTIDs)
In the event of Server "A" crashing, we need to failover to one of the slaves, promoting it to master, while the remaining server becomes a slave of that new master
As MySQL replication is asynchronous by default,servers B and C may not have both replicated and executed the same set of transactions
A是原来的主服务器，B是第一个从服务器，C是第二个从服务器，为了实现高可用的功能，当A主机节点挂掉的时候，要将B或者C一个提升为主服务器，如果将B提升为主的，那么C将要修改他的主服务器为B了，要从B复制数据了，考虑一种场景B和C由于是异步的，B有可能落后于A，C有可能也要落后于A，那B和C他俩谁快谁慢，这很难说，一般应该找一个快的作为下一个新的主服务器，但问题是就算你快一点也未必C执行的B都执行过，虽然C要比B快一些，比如说A让执行了10个事物，B上已经完成了9个，C上才完成3个，但B完成的是1-9，C完成的是8-10，C上有个10的事物B上并没有完成，这时候应该以谁为准，我们把B提升为主的之前必须要保证C上有的B上没有的都应该先复制到B上去，然后再把B提升为主的，再让C成为B的从，像早起没有gtid的时候B上已经执行的事物和C上已经执行的事物谁执行的多谁执行的少，它怎么去区别，我们记录在二进制日志里面的那些事件，每一个事物到底属于那个事物并没有明确说明，所以主从复制的时候只能挨个一个一个进行复制，在那种场景下一旦A产生故障，要想把其中一个提升为主的，那就麻烦大了，因为B虽然比C快，但B并不意味包含C所有已经生效的内容，这时候不不得不把C先同步到B，然后再把B提升为主的，那问题是B怎么知道C中的那些B中没有，它怎么追中这些功能，怎么追踪这些相关的事件，追踪不到，由此gtid此处就能派上用场了，每一个事物在记录到二进制日志中的时候它这个二进制日志的事件会明确标明这个事件属于那个gtid的，那么gtid中有事物的编号，所以B只需要比较一下通知给C已经完成那些事物了，C看一看本地那些事物已经有但B中没有的，于是可以把这些事物提供给B，而后当这一切结束，C再成为B的从，而且有了gtid之后由于每一个二进制日志当中都有其相关的事物的相关信息，所以我们从此以后再实现复制的时候再也不需要指定master-log-file等参数了，因为他们主机双方会通过协议协商一下看一看已经执行了那些事物，而对方那边有那些事物我这边还没有，我就从那里开始复制，所以有了gtid之后我们可以完全自动让主从两端自动去发现它应该从那里复制，从那个文件复制，从服务器端再也不需要指了，有了gtid以后使得mysql的复制架构可以快速从崩溃中恢复回来，在一定程度上它提高了高可用能力；
Multi_Threaded Slaves（多线程复制）
The multi-threaded slave splits processing beteween worker threads based on worker threads based on schema, allowing updates to be applied in parallel,rather than sequentially
The number of worker threads is configured using the slave-parallel-workers（从服务器并行的线程数） parameter
有了gtid这样的相关功能之后，从服务器上所谓的多线程复制，它指的是我们的主服务器由于有多颗CPU，它的多个事物执行的时候很有可能产生二进制日志的速度要比直接记录进磁盘I/O中的速度，甚至于比传递到从服务器上的速度都要快，从服务器上只有一个io threaded不停的从主服务器复制数据的，复制完以后把数据保存到本地的中继日志当中，而后sql threaded来完成数据的重放，sql threaded负责从中继日志中读一个事件重放一个事件，进而将数据保存到本地的数据库当中，在主服务器上多个事物在多颗CPU上同时执行，也就意味着它产生的速度比应用本地重放的速度要快的多，因为重放只有一个线程，而多线程模型下，多个事物同时执行的时候很显然主服务器比从服务器要快很多，这样会导致我们的从服务器比主服务器要落后很多的，比了避免这种情况从服务器可以启动多个sql threaded并行的读数据，并且将它们重放到本地，这样以来对于magedudb数据库来讲它里面可能有很多张表，而且这些表之间相关的那些数据修改之间的操作能不能使用两个线程在这里交叉往本地服务器应用，如果交叉来那些数据有前后次序，在一个事物上前后语句的顺序是非常关键的，如果交叉执行，服务器会错乱，这是绝对不允许的，所以mysql 5.6的多线程复制指的是每一个数据库也仅能使用一个线程，所以复制的时候只复制了一个数据库，多线程也没有用，所以只有在复制环境当中，如果有多个数据库这个时候多线程复制才有意义的；
每个数据库仅能使用一个线程
复制涉及到多个数据库时多线程才有意义
Utilities for Simplifying Replication
For convenience and ease-of-use, additional MySQL Replication Utilities are provided to accelerate the provisioning of new replication clusters
The utilities are available under the GPLv2 license, and are extendable using a supplied library
They are designed to work with Python 2.7 and above（依赖于python 2.7，最好使用rhel 6版本）
https://launchpad.net/mysql-utilities（下载地址）
Replicate（复制相关操作）-- Check（检查相关操作） -- Show（显示相关命令）-- HA（高可用相关命令）
Utilities for Simplifying Replication
Replication Utility: mysqlreplicate（跟复制相关工具）
Enables fast and simple introduction of replication slaves, the mysqlreplicate utility is used to start the replication process（能够实现快速启用mysql的从服务器，能够帮助从服务器检查本地所有已经在本地执行的事物）
Any GTIDs that have already been executed on the slave will be skipped（通过追踪gtid知道那些事物已经完成了，通过跳过这些已经执行的事物，快速从那些尚未执行的事物开始）
The utility also checks storage engine compatibility
Replication Utility: mysqlrplcheck（检查工具）
Provides simple verification of deployment and fast fault resolution（用于实现简单的验证我们的部署并实现快速故障解决等相关功能）
Checks that the binlog is enabled and displays any configured exceptions（能够检查我们binlog是不是被启用了，以及能显示相关的配置异常）
Checks slave access and privileges to master and slave connection status
Replication Utility: mysqlrplshow（显示命令）
Discovers and displays the replication toplogy on-demand（发现并显示我们的复制拓扑图的）
Shows slaves attached to each master and labels each slave with hostname and port number（在显示主服务器和从服务器会显示主机名和端口号）
Replication Utility:mysqlfailover（故障转移工具）
Enables automatic or manual failover to a slave in the event of an outage to the master（能够快速自动或者手动提升一个slave为master）
Replication Utility:mysqlrpladmin（调度管理工具）
If a user needs to take a master offline for scheduled maintenance,mysqlrpladmin can perform a switchover to a specific slave (called the new master)
MySQL 5.6引入的GTID(Global Transaction IDs)使得其复制功能的配置、监控及管理变得更加易于实现，且更加健壮。
要在MySQL 5.6中使用复制功能，其服务配置段[mysqld]中于少应该定义如下选项：
binlog-format：二进制日志的格式，有row、statement和mixed几种类型；
需要注意的是：当设置隔离级别为READ-COMMITED必须设置二进制日志格式为ROW，现在MySQL官方认为STATEMENT这个已经不再适合继续使用；但mixed类型在默认的事务隔离级别下，可能会导致主从数据不一致；
log-slave-updates（当从服务器上从中继日志中读取事件在本地应用的时候是否把这个事件写操作的相关操作记录到本地二进制日志）、gtid-mode（是否启用gtid功能）、enforce-gtid-consistency（是否强制gtid具有其一致性功能）、report-port（在gtid模式下每一个从服务器在连入主服务器的时候它必须要告诉主服务器主机是谁，自己的主机名或ip地址以及端口号）和report-host（主机主机名）：用于启动GTID及满足附属的其它需求；
master-info-repository和relay-log-info-repository：启用此两项，可用于实现在崩溃时保证二进制及从服务器安全的功能；
sync-master-info（同步master info，启动从服务器以后，从服务器应该把它的master info，启动的相关信息随时记录到master info里面，任何一个事物提交之后必须要把这个事物提交以后那个读取的二进制日志事件的位置对应的文件名称都要记录到master info中，下一次启动就可以从这个位置读取）：启用之可确保无信息丢失；
slave-paralles-workers：设定从服务器的SQL线程数；0表示关闭多线程复制功能；
binlog-checksum（主服务器端在启动的时候要不要校验binlog本身的校验码，比如这次崩溃了下次启动发现binlog二进制日志，默认会检查的，发现这个二进制日志校验码跟上次停机的校验码不一样它会拒绝读取的）、master-verify-checksum和slave-sql-verify-checksum：启用复制有关的所有校验功能；
binlog-rows-query-log-events：启用之可用于在二进制日志记录事件相关的信息，可降低故障排除的复杂度；
log-bin：启用二进制日志，这是保证复制功能的基本前提；
server-id：同一个复制拓扑中的所有服务器的id号必须惟一；
report-host：
The host name or IP address of the slave to be reported to the master during slave registration. This value appears in the output of SHOW SLAVE HOSTS on the master server.
report-port:
The TCP/IP port number for connecting to the slave, to be reported to the master during slave registration.
master-info-repository:
The setting of this variable determines whether the slave logs master status and connection information to a FILE (master.info), or to a TABLE (mysql.slave_master_info)
relay-log-info-repository：
This option causes the server to log its relay log info to a file or a table.
log_slave_updates：
Whether updates received by a slave server from a master server should be logged to the slave's own binary log. Binary logging must be enabled on the slave for this variable to have any effect.
enforce_gtid_consistency：
一、简单主从模式配置步骤
1、配置主从节点的服务配置文件
1.1、配置master节点：
[mysqld]
binlog-format=ROW
log-bin=master-bin
log-slave-updates=true
gtid-mode=on
enforce-gtid-consistency=true
master-info-repository=TABLE（用于让主服务器记录每一个从服务器它的连接信息以及每一个从服务器所复制的二进制日志的文件名极其相关事件位置等信息，可以记录在file和table，默认为file）
relay-log-info-repository=TABLE（让从服务器自己记录连接的主服务器是谁，主服务器那一个二进制文件以及那个二进制文件的什么位置等待，可以记录在file和table中）
sync-master-info=1
slave-parallel-workers=2
binlog-checksum=CRC32
master-verify-checksum=1
slave-sql-verify-checksum=1
binlog-rows-query-log_events=1
server-id=1
report-port=3306
port=3306
datadir=/mydata/data
socket=/tmp/mysql.sock
report-host=master.magedu.com
1.2、配置slave节点：（从节点不用定义二进制日志）
[mysqld]
binlog-format=ROW
log-slave-updates=true
gtid-mode=on
enforce-gtid-consistency=true
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
slave-parallel-workers=2
binlog-checksum=CRC32
master-verify-checksum=1
slave-sql-verify-checksum=1
binlog-rows-query-log_events=1
server-id=11
report-port=3306
port=3306
log-bin=mysql-bin.log
datadir=/mydata/data
socket=/tmp/mysql.sock
report-host=slave.magedu.com
2、创建复制用户
mysql> GRANT REPLICATION SLAVE ON *.* TO repluser@172.16.100.7 IDENTIFIED BY 'replpass';
说明：172.16.100.7是从节点服务器；如果想一次性授权更多的节点，可以自行根据需要修改；
3、为备节点提供初始数据集
锁定主表，备份主节点上的数据，将其还原至从节点；如果没有启用GTID，在备份时需要在master上使用show master status命令查看二进制日志文件名称及事件位置，以便后面启动slave节点时使用。
4、启动从节点的复制线程
如果启用了GTID功能，则使用如下命令：
mysql> CHANGE MASTER TO MASTER_HOST='master.magedu.com', MASTER_USER='repluser', MASTER_PASSWORD='replpass', MASTER_AUTO_POSITION=1;
没启用GTID，需要使用如下命令：
slave> CHANGE MASTER TO MASTER_HOST='172.16.100.6',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='replpass',
-> MASTER_LOG_FILE='master-bin.000003',
-> MASTER_LOG_POS=1174;
二、半同步复制
1、分别在主从节点上安装相关的插件
master> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
slave> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
2、启用半同步复制
在master上的配置文件中，添加
rpl_semi_sync_master_enabled=ON
在至少一个slave节点的配置文件中添加
rpl_semi_sync_slave_enabled=ON
而后重新启动mysql服务即可生效。
或者，也可以mysql服务上动态启动其相关功能：
master> SET GLOBAL rpl_semi_sync_master_enabled = ON;
slave> SET GLOBAL rpl_semi_sync_slave_enabled = ON;
slave> STOP SLAVE IO_THREAD; START SLAVE IO_THREAD;
3、确认半同步功能已经启用
master> CREATE DATABASE magedudb;
master> SHOW STATUS LIKE 'Rpl_semi_sync_master_yes_tx';
slave> SHOW DATABASES;
node1:
[root@node1 ~]# mysql（连接mysql服务器） Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 12 Server version: 5.5.28-log MySQL Community Server (GPL) Copyright (c) 2000, 2012, 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 GLOBAL VARIABLES LIKE 'binlog%';（查看全局变量binlog相关） +-----------------------------------------+-------+ | Variable_name | Value | +-----------------------------------------+-------+ | binlog_cache_size | 32768 | | binlog_direct_non_transactional_updates | OFF | | binlog_format | MIXED | | binlog_stmt_cache_size | 32768 | +-----------------------------------------+-------+ 4 rows in set (0.00 sec)
node2:
[root@node2 ~]# vim /etc/my.cnf（编辑数据库配置文件）
replicate-do-db = discuz（只从主mysql数据库复制discuz库）
[root@node2 ~]# service mysqld restart（重启mysqld服务）
Shutting down MySQL. [确定]
Starting MySQL. [确定]
[root@node2 ~]# mysql（连接mysql）
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.28 MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> SHOW VARIABLES LIKE 'rep%';（查看全局变量rep相关参数）
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| report_host | |
| report_password | |
| report_port | 3306 |
| report_user | |
+-----------------+-------+
4 rows in set (0.00 sec)
提示：不再服务器变量中显示，说明这个变量是只读的不允许在服务器运行时修改；
mysql> SHOW SLAVE STATUS\G（查看从mysql状态，\G树状显示）
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.100.6
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 1351
Relay_Log_File: relay-log.000010
Relay_Log_Pos: 254
Relay_Master_Log_File: master-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: discuz（只复制discuz数据库）
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: 1351
Relay_Log_Space: 404
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
1 row in set (0.00 sec)
node1:
mysql> CREATE DATABASE magedudb;（创建magedudb数据库） Query OK, 1 row affected (0.00 sec) mysql> SHOW DATABASES;（显示数据库） +--------------------+ | Database | +--------------------+ | information_schema | | magedudb | | mysql | | performance_schema | +--------------------+ 4 rows in set (0.01 sec)
node2:
mysql> SHOW DATABASES;（显示数据库） +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | +--------------------+ 3 rows in set (0.00 sec) 提示：从mysql服务器没有复制magedudb数据库；
node1:
mysql> CREATE DATABASE discuz;（创建discuz数据库） Query OK, 1 row affected (0.00 sec)
node2:
mysql> SHOW DATABASES;（显示数据库） +--------------------+ | Database | +--------------------+ | information_schema | | discuz | | mysql | | performance_schema | +--------------------+ 4 rows in set (0.00 sec) 提示：从主mysql服务器复制过来discuz数据库；
node1:
mysql> SHOW DATABASES;（显示数据库） +--------------------+ | Database | +--------------------+ | information_schema | | discuz | | magedudb | | mysql | | performance_schema | +--------------------+ 5 rows in set (0.00 sec) mysql> SHOW SLAVE HOSTS;（查看已经连入到主服务器的从服务器） +-----------+------+------+-----------+ | Server_id | Host | Port | Master_id | +-----------+------+------+-----------+ | 11 | | 3306 | 1 | +-----------+------+------+-----------+ 1 row in set (0.00 sec)
环境：两台rhel6.4版本的linux，其中一台作为主mysql服务器，地址为172.16.100.106，另一台作为备用mysql服务器，地址为172.16.100.107；
master:
[root@Smoke ~]# ifconfig eth0
eth0 Link encap:Ethernet HWaddr 00:0C:29:67:2C:95
inet addr:172.16.100.106 Bcast:172.16.100.255 Mask:255.255.255.0
inet6 addr: fe80::20c:29ff:fe67:2c95/64 Scope:Link
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
RX packets:507 errors:0 dropped:0 overruns:0 frame:0
TX packets:420 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:49416 (48.2 KiB) TX bytes:47548 (46.4 KiB)
Interrupt:19 Base address:0x2000
[root@Smoke ~]# ntpdate 172.16.100.30（向ntp服务器同步时间）
19 Jun 19:30:06 ntpdate[2297]: step time server 172.16.100.30 offset -307105.204616 sec
[root@Smoke ~]# hostname master.magedu.com（修改主机名）
[root@Smoke ~]# hostname（查看主机名）
master.magedu.com
[root@Smoke ~]# vim /etc/sysconfig/network（编辑主机名配置文件）
NETWORKING=yes
HOSTNAME=master.magedu.com
[root@Smoke ~]# uname -n（查看主机名）
master.magedu.com
[root@master ~]# vim /etc/hosts（编辑本地解析文件）
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
172.16.100.106 master.magedu.com master
172.16.100.107 slave.magedu.com slave
[root@master ~]# ssh-keygen -t rsa -f ~/.ssh/id_rsa -P ''（生成一对密钥，-t指定加密算法类型rsa或dsa，-f指定私钥文件保存位置，-P指定私钥密码）
Generating public/private rsa key pair.
Your identification has been saved in /root/.ssh/id_rsa.
Your public key has been saved in /root/.ssh/id_rsa.pub.
The key fingerprint is:
83:86:b7:1e:bd:f1:38:f1:05:6c:a6:df:71:71:2a:b9 root@master.magedu.com
The key's randomart image is:
+--[ RSA 2048]----+
| |
| |
| . |
| . . = . .|
| . + S+ . . + |
| o oo. = o |
| o o+ o = |
| . ..=o E |
| . o.. |
+-----------------+
[root@master ~]# ssh-copy-id -i .ssh/id_rsa.pub root@172.16.100.107（通过ssh-copy-id将.ssh/id_rsa.pub公钥文件复制到远程主机172.16.100.107，以root
用户登录，-i指定公钥文件）
The authenticity of host '172.16.100.107 (172.16.100.107)' can't be established.
RSA key fingerprint is 36:33:b8:fe:9f:98:4a:7b:06:f7:b7:e4:62:66:6f:d4.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '172.16.100.107' (RSA) to the list of known hosts.
root@172.16.100.107's password:
Now try logging into the machine, with "ssh 'root@172.16.100.107'", and check in:
.ssh/authorized_keys
to make sure we haven't added extra keys that you weren't expecting.
slave:
[root@Smoke ~]# ifconfig eth0（查看eth0网卡信息）
eth0 Link encap:Ethernet HWaddr 00:0C:29:80:E1:12
inet addr:172.16.100.107 Bcast:172.16.100.255 Mask:255.255.255.0
inet6 addr: fe80::20c:29ff:fe80:e112/64 Scope:Link
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
RX packets:1458 errors:0 dropped:0 overruns:0 frame:0
TX packets:1780 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:119287 (116.4 KiB) TX bytes:784969 (766.5 KiB)
Interrupt:19 Base address:0x2000
[root@Smoke ~]# ntpdate 172.16.100.30（向ntp服务器同步时间）
23 Jun 21:39:52 ntpdate[2012]: step time server 172.16.100.30 offset 45599.154327 sec
[root@Smoke ~]# hostname slave.magedu.com（修改主机名）
[root@Smoke ~]# hostname（查看主机名）
slave.magedu.com
[root@Smoke ~]# vim /etc/sysconfig/network（修改主机名配置文件）
NETWORKING=yes
HOSTNAME=slave.magedu.com
[root@Smoke ~]# uname -n（查看主机名）
slave.magedu.com
[root@slave ~]# vim /etc/hosts（编辑本地解析配置文件）
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
172.16.100.106 master.magedu.com master
172.16.100.107 slave.magedu.com slave
[root@slave ~]# ssh-keygen -t rsa -f ~/.ssh/id_rsa -P ''（生成一对密钥，指定加密算法类型rsa或dsa，-f指定密钥文件保存位置，-P指定私钥密码）
Generating public/private rsa key pair.
Your identification has been saved in /root/.ssh/id_rsa.
Your public key has been saved in /root/.ssh/id_rsa.pub.
The key fingerprint is:
e5:18:96:98:ed:e5:88:d6:53:d0:5e:f3:0c:37:d0:36 root@slave.magedu.com
The key's randomart image is:
+--[ RSA 2048]----+
| .. .o |
| + o. + E |
| o =.+. B o |
| = X. o |
| o S o |
| . . |
| |
| |
| |
+-----------------+
[root@slave ~]# ssh-copy-id -i .ssh/id_rsa.pub root@172.16.100.106（通过ssh-copy-id将.ssh/id_rsa.pub公钥文件复制到远程主机172.16.100.106，以root用户
登录，-i指定公钥文件）
The authenticity of host '172.16.100.106 (172.16.100.106)' can't be established.
RSA key fingerprint is 8c:97:d3:b0:6e:6f:d2:bf:94:21:bc:bb:14:e1:9e:c1.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '172.16.100.106' (RSA) to the list of known hosts.
root@172.16.100.106's password:
Now try logging into the machine, with "ssh 'root@172.16.100.106'", and check in:
.ssh/authorized_keys
to make sure we haven't added extra keys that you weren't expecting.
master:
[root@master ~]# uname -r（查看内核版本） 2.6.32-358.el6.i686 [root@master ~]# lsb_release（查看系统内核信息） LSB Version: :base-4.0-ia32:base-4.0-noarch:core-4.0-ia32:core-4.0-noarch:graphics-4.0-ia32:graphics-4.0-noarch:printing-4.0-ia32:printing -4.0-noarch [root@master ~]# cat /etc/issue（查看issue文件内容） Red Hat Enterprise Linux Server release 6.4 (Santiago) Kernel \r on an \m
slave:
[root@slave ~]# uname -n（查看主机名） slave.magedu.com [root@slave ~]# uname -r（查看内核版本） 2.6.32-358.el6.i686
master:
[root@master ~]# uname -n（查看主机名） master.magedu.com [root@master ~]# lftp 172.16.0.1（连接ftp服务器） cd ok, cwd=/pub/Sources lftp 172.16.0.1:/pub/Sources> cd mysql-5.6（切换到mysql-5.6目录） lftp 172.16.0.1:/pub/SOurces/mysql-5.6> get mysql-5.6.10-linux-glibc2.5-i686.tar.gz（下载mysql-5.6.10） lftp 172.16.0.1:/pub/SOurces/mysql-5.6> bye（退出）
slave:
[root@slave ~]# lftp 172.16.0.1（连接ftp服务器） cd ok, cwd=/pub/Sources lftp 172.16.0.1:/pub/Sources> cd mysql-5.6（切换到mysql-5.6目录） lftp 172.16.0.1:/pub/SOurces/mysql-5.6> get mysql-5.6.10-linux-glibc2.5-i686.tar.gz（下载mysql-5.6.10） lftp 172.16.0.1:/pub/SOurces/mysql-5.6> bye（退出）
master:
[root@master ~]# mkdir -pv /mydata/data（创建/mydata/data目录，-p递归创建，-v显示创建过程） mkdir: 已创建目录 "/mydata" mkdir: 已创建目录 "/mydata/data" [root@master ~]# useradd -r mysql（创建mysql系统用户） [root@master ~]# chown -R mysql.mysql /mydata/data/（更改/mydata/data的属主属组为mysql） [root@master ~]# tar xf mysql-5.6.10-linux-glibc2.5-i686.tar.gz -C /usr/local/（解压mysql-5.6.10到/usr/local目录，x解压，f后面跟文件名，-C更改解压目录）
slave:
[root@slave ~]# mkdir -pv /mydata/data（创建/mydata/data目录，-p递归创建，-v显示创建过程） mkdir: 已创建目录 "/mydata" mkdir: 已创建目录 "/mydata/data" [root@slave ~]# useradd -r mysql（创建系统用户mysql，-r系统用户） [root@slave ~]# chown -R mysql.mysql /mydata/data/（更改/mydata/data目录属主属组为mysql，-R递归更改）
master:
[root@master ~]# cd /usr/local/（切换到/usr/local目录） [root@master local]# ln -sv mysql-5.6.10-linux-glibc2.5-i686 mysql（给mysql-5.6.10创建软连接叫mysql，-s软连接，-v显示创建过程） "mysql" -> "mysql-5.6.10-linux-glibc2.5-i686" [root@master local]# cd mysql（切换到mysql目录） [root@master mysql]# ll（查看当前目录文件及子目录详细信息） 总用量 76 drwxr-xr-x. 2 root root 4096 6月 23 22:14 bin -rw-r--r--. 1 7161 wheel 17987 1月 23 2013 COPYING drwxr-xr-x. 4 root root 4096 6月 23 22:14 data drwxr-xr-x. 2 root root 4096 6月 23 22:14 docs drwxr-xr-x. 3 root root 4096 6月 23 22:14 include -rw-r--r--. 1 7161 wheel 7468 1月 23 2013 INSTALL-BINARY drwxr-xr-x. 3 root root 4096 6月 23 22:14 lib drwxr-xr-x. 4 root root 4096 6月 23 22:14 man drwxr-xr-x. 10 root root 4096 6月 23 22:14 mysql-test -rw-r--r--. 1 7161 wheel 2552 1月 23 2013 README drwxr-xr-x. 2 root root 4096 6月 23 22:14 scripts drwxr-xr-x. 28 root root 4096 6月 23 22:14 share drwxr-xr-x. 4 root root 4096 6月 23 22:14 sql-bench drwxr-xr-x. 3 root root 4096 6月 23 22:14 support-files [root@master mysql]# chown -R root.mysql ./*（更改当前目录所有文件的属主为root，属组为mysql，-R递归更改） [root@master mysql]# ll（查看当前目录文件及子目录详细信息） 总用量 76 drwxr-xr-x. 2 root mysql 4096 6月 23 22:14 bin -rw-r--r--. 1 root mysql 17987 1月 23 2013 COPYING drwxr-xr-x. 4 root mysql 4096 6月 23 22:14 data drwxr-xr-x. 2 root mysql 4096 6月 23 22:14 docs drwxr-xr-x. 3 root mysql 4096 6月 23 22:14 include -rw-r--r--. 1 root mysql 7468 1月 23 2013 INSTALL-BINARY drwxr-xr-x. 3 root mysql 4096 6月 23 22:14 lib drwxr-xr-x. 4 root mysql 4096 6月 23 22:14 man drwxr-xr-x. 10 root mysql 4096 6月 23 22:14 mysql-test -rw-r--r--. 1 root mysql 2552 1月 23 2013 README drwxr-xr-x. 2 root mysql 4096 6月 23 22:14 scripts drwxr-xr-x. 28 root mysql 4096 6月 23 22:14 share drwxr-xr-x. 4 root mysql 4096 6月 23 22:14 sql-bench drwxr-xr-x. 3 root mysql 4096 6月 23 22:14 support-files [root@master mysql]# scripts/mysql_install_db --user=mysql --datadir=/mydata/data（初始化mysql，--user指定运行mysql服务用户，--datadir指定数据目录） Installing MySQL system tables...2016-06-23 22:25:32 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit _defaults_for_timestamp server option (see documentation for more details). 2016-06-23 22:25:32 2597 [Note] InnoDB: The InnoDB memory heap is disabled 2016-06-23 22:25:32 2597 [Note] InnoDB: Mutexes and rw_locks use InnoDB's own implementation 2016-06-23 22:25:32 2597 [Note] InnoDB: Compressed tables use zlib 1.2.3 2016-06-23 22:25:32 2597 [Note] InnoDB: CPU does not support crc32 instructions 2016-06-23 22:25:32 2597 [Note] InnoDB: Using Linux native AIO 2016-06-23 22:25:32 2597 [Note] InnoDB: Initializing buffer pool, size = 128.0M 2016-06-23 22:25:32 2597 [Note] InnoDB: Completed initialization of buffer pool 2016-06-23 22:25:32 2597 [Note] InnoDB: The first specified data file ./ibdata1 did not exist: a new database to be created! 2016-06-23 22:25:32 2597 [Note] InnoDB: Setting file ./ibdata1 size to 12 MB 2016-06-23 22:25:32 2597 [Note] InnoDB: Database physically writes the file full: wait... 2016-06-23 22:25:32 2597 [Note] InnoDB: Setting log file ./ib_logfile101 size to 48 MB 2016-06-23 22:25:34 2597 [Note] InnoDB: Setting log file ./ib_logfile1 size to 48 MB 2016-06-23 22:25:35 2597 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0 2016-06-23 22:25:35 2597 [Warning] InnoDB: New log files created, LSN=45781 2016-06-23 22:25:35 2597 [Note] InnoDB: Doublewrite buffer not found: creating new 2016-06-23 22:25:35 2597 [Note] InnoDB: Doublewrite buffer created 2016-06-23 22:25:35 2597 [Note] InnoDB: 128 rollback segment(s) are active. 2016-06-23 22:25:35 2597 [Warning] InnoDB: Creating foreign key constraint system tables. 2016-06-23 22:25:35 2597 [Note] InnoDB: Foreign key constraint system tables created 2016-06-23 22:25:35 2597 [Note] InnoDB: Creating tablespace and datafile system tables. 2016-06-23 22:25:35 2597 [Note] InnoDB: Tablespace and datafile system tables created. 2016-06-23 22:25:35 2597 [Note] InnoDB: Waiting for purge to start 2016-06-23 22:25:35 2597 [Note] InnoDB: 1.2.10 started; log sequence number 0 2016-06-23 22:25:36 2597 [Note] Binlog end 2016-06-23 22:25:36 2597 [Note] InnoDB: FTS optimize thread exiting. 2016-06-23 22:25:36 2597 [Note] InnoDB: Starting shutdown... 2016-06-23 22:25:37 2597 [Note] InnoDB: Shutdown completed; log sequence number 1625977 OK Filling help tables...2016-06-23 22:25:37 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for _timestamp server option (see documentation for more details). 2016-06-23 22:25:37 2620 [Note] InnoDB: The InnoDB memory heap is disabled 2016-06-23 22:25:37 2620 [Note] InnoDB: Mutexes and rw_locks use InnoDB's own implementation 2016-06-23 22:25:37 2620 [Note] InnoDB: Compressed tables use zlib 1.2.3 2016-06-23 22:25:37 2620 [Note] InnoDB: CPU does not support crc32 instructions 2016-06-23 22:25:37 2620 [Note] InnoDB: Using Linux native AIO 2016-06-23 22:25:37 2620 [Note] InnoDB: Initializing buffer pool, size = 128.0M 2016-06-23 22:25:37 2620 [Note] InnoDB: Completed initialization of buffer pool 2016-06-23 22:25:37 2620 [Note] InnoDB: Highest supported file format is Barracuda. 2016-06-23 22:25:37 2620 [Note] InnoDB: 128 rollback segment(s) are active. 2016-06-23 22:25:37 2620 [Note] InnoDB: Waiting for purge to start 2016-06-23 22:25:37 2620 [Note] InnoDB: 1.2.10 started; log sequence number 1625977 2016-06-23 22:25:37 2620 [Note] Binlog end 2016-06-23 22:25:37 2620 [Note] InnoDB: FTS optimize thread exiting. 2016-06-23 22:25:37 2620 [Note] InnoDB: Starting shutdown... 2016-06-23 22:25:39 2620 [Note] InnoDB: Shutdown completed; log sequence number 1625987 OK To start mysqld at boot time you have to copy support-files/mysql.server to the right place for your system PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! To do so, start the server, then issue the following commands: ./bin/mysqladmin -u root password 'new-password' ./bin/mysqladmin -u root -h master.magedu.com password 'new-password' Alternatively you can run: ./bin/mysql_secure_installation which will also give you the option of removing the test databases and anonymous user created by default. This is strongly recommended for production servers. See the manual for more instructions. You can start the MySQL daemon with: cd . ; ./bin/mysqld_safe & You can test the MySQL daemon with mysql-test-run.pl cd mysql-test ; perl mysql-test-run.pl Please report any problems with the ./bin/mysqlbug script! The latest information about MySQL is available on the web at http://www.mysql.com Support MySQL by buying support/licenses at http://shop.mysql.com WARNING: Found existing config file ./my.cnf on the system. Because this file might be in use, it was not replaced, but was used in bootstrap (unless you used --defaults-file) and when you later start the server. The new default config file was created as ./my-new.cnf, please compare it with your file and take the changes you need. WARNING: Default config file /etc/my.cnf exists on the system This file will be read by default by the MySQL server If you do not want to use this, either remove it, or use the --defaults-file argument to mysqld_safe when starting the server [root@master mysql]# ls（查看当前目录文件及子目录） bin COPYING data docs include INSTALL-BINARY lib man my.cnf my-new.cnf mysql-test README scripts share sql-bench support-files 提示：初始化完成之后会在当前目录生成一个my.cnf配置文件，可以不往/etc下复制，直接编辑这个就行； [root@master mysql]# cp support-files/mysql.server /etc/init.d/mysqld（复制mysql.server服务器启动脚本到/etc/init.d目录叫mysqld） [root@master mysql]# chkconfig --add mysqld（添加mysqld服务到服务列表） [root@master mysql]# vim my.cnf（编辑my.cnf配置文件） datadir = /mydata/data（指定数据目录） innodb_file_per_table = ON（启动每表一个表结构文件） server-id = 1（指定server id） socket = /tmp/mysql.sock（指定套接字文件） log-bin = master-bin（启动二进制日志文件） [root@master mysql]# service mysqld start（启动mysqld服务） Starting MySQL. [确定] [root@master mysql]# netstat -tunlp（显示系统服务，-t代表tcp，-u代表udp，-n以数字显示，-l坚监听端口，-p显示服务名称） Active Internet connections (only servers) 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 1047/sshd tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN 1123/master tcp 0 0 127.0.0.1:6010 0.0.0.0:* LISTEN 1871/sshd tcp 0 0 127.0.0.1:6012 0.0.0.0:* LISTEN 2333/sshd tcp 0 0 :::3306 :::* LISTEN 2906/mysqld tcp 0 0 :::22 :::* LISTEN 1047/sshd tcp 0 0 ::1:25 :::* LISTEN 1123/master tcp 0 0 ::1:6010 :::* LISTEN 1871/sshd tcp 0 0 ::1:6012 :::* LISTEN 2333/sshd [root@master mysql]# vim /etc/profile.d/mysql.sh（编辑mysql.sh的PATH环境变量文件） export PATH=$PATH:/usr/local/mysql/bin [root@master mysql]# . /etc/profile.d/mysql.sh（重读mysql.sh文件） [root@master mysql]# mysql（连接mysql） Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.6.10-log MySQL Community Server (GPL) Copyright (c) 2000, 2013, 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> \q（退出） Bye [root@master mysql]# ls（查看当前目录文件及子目录） bin COPYING data docs include INSTALL-BINARY lib man my.cnf my-new.cnf mysql-test README scripts share sql-bench support-files [root@master mysql]# ls /mydata/data/（查看/mydata/data目录文件及子目录） auto.cnf ibdata1 ib_logfile0（innodb的事物日志） ib_logfile1 master-bin.000001 master-bin.index master.magedu.com.pid mysql performance _schema test
slave:
[root@slave ~]# ls（查看当前目录文件及子目录） anaconda-ks.cfg install.log install.log.syslog mysql-5.6.10-linux-glibc2.5-i686.tar.gz [root@slave ~]# tar xf mysql-5.6.10-linux-glibc2.5-i686.tar.gz -C /usr/local/（解压mysql-5.6.10，x解压，f后面跟文件，-C更改解压目录） [root@slave ~]# cd /usr/local/（切换到/usr/local目录） [root@slave local]# ls（查看当前目录文件及子目录） bin etc games include lib libexec mysql-5.6.10-linux-glibc2.5-i686 sbin share src [root@slave local]# ln -sv mysql-5.6.10-linux-glibc2.5-i686 mysql（给mysql-5.6.10创建软连接,-s软连接，-v显示创建过程，） "mysql" -> "mysql-5.6.10-linux-glibc2.5-i686" [root@slave local]# ll（查看当前目录文件及子目录） 总用量 40 drwxr-xr-x. 2 root root 4096 6月 28 2011 bin drwxr-xr-x. 2 root root 4096 6月 28 2011 etc drwxr-xr-x. 2 root root 4096 6月 28 2011 games drwxr-xr-x. 2 root root 4096 6月 28 2011 include drwxr-xr-x. 2 root root 4096 6月 28 2011 lib drwxr-xr-x. 2 root root 4096 6月 28 2011 libexec lrwxrwxrwx. 1 root root 32 6月 23 22:44 mysql -> mysql-5.6.10-linux-glibc2.5-i686 drwxr-xr-x. 13 root root 4096 6月 23 22:43 mysql-5.6.10-linux-glibc2.5-i686 drwxr-xr-x. 2 root root 4096 6月 28 2011 sbin drwxr-xr-x. 5 root root 4096 6月 23 07:18 share drwxr-xr-x. 2 root root 4096 6月 28 2011 src [root@slave local]# chown -R root.mysql ./*（更改当前目录所有文件的属主为root，属组为mysql，-R递归更改） [root@slave mysql]# scripts/mysql_install_db --user=mysql --datadir=/mydata/data（初始化mysql，--user指定运行mysql服务的用户，--datadir指定数据目录） Installing MySQL system tables...2016-06-23 22:55:07 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit _defaults_for_timestamp server option (see documentation for more details). 2016-06-23 22:55:07 2218 [Note] InnoDB: The InnoDB memory heap is disabled 2016-06-23 22:55:07 2218 [Note] InnoDB: Mutexes and rw_locks use InnoDB's own implementation 2016-06-23 22:55:07 2218 [Note] InnoDB: Compressed tables use zlib 1.2.3 2016-06-23 22:55:07 2218 [Note] InnoDB: CPU does not support crc32 instructions 2016-06-23 22:55:07 2218 [Note] InnoDB: Using Linux native AIO 2016-06-23 22:55:07 2218 [Note] InnoDB: Initializing buffer pool, size = 128.0M 2016-06-23 22:55:08 2218 [Note] InnoDB: Completed initialization of buffer pool 2016-06-23 22:55:08 2218 [Note] InnoDB: The first specified data file ./ibdata1 did not exist: a new database to be created! 2016-06-23 22:55:08 2218 [Note] InnoDB: Setting file ./ibdata1 size to 12 MB 2016-06-23 22:55:08 2218 [Note] InnoDB: Database physically writes the file full: wait... 2016-06-23 22:55:08 2218 [Note] InnoDB: Setting log file ./ib_logfile101 size to 48 MB 2016-06-23 22:55:11 2218 [Note] InnoDB: Setting log file ./ib_logfile1 size to 48 MB 2016-06-23 22:55:14 2218 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0 2016-06-23 22:55:14 2218 [Warning] InnoDB: New log files created, LSN=45781 2016-06-23 22:55:14 2218 [Note] InnoDB: Doublewrite buffer not found: creating new 2016-06-23 22:55:14 2218 [Note] InnoDB: Doublewrite buffer created 2016-06-23 22:55:14 2218 [Note] InnoDB: 128 rollback segment(s) are active. 2016-06-23 22:55:14 2218 [Warning] InnoDB: Creating foreign key constraint system tables. 2016-06-23 22:55:14 2218 [Note] InnoDB: Foreign key constraint system tables created 2016-06-23 22:55:14 2218 [Note] InnoDB: Creating tablespace and datafile system tables. 2016-06-23 22:55:14 2218 [Note] InnoDB: Tablespace and datafile system tables created. 2016-06-23 22:55:14 2218 [Note] InnoDB: Waiting for purge to start 2016-06-23 22:55:14 2218 [Note] InnoDB: 1.2.10 started; log sequence number 0 2016-06-23 22:55:16 2218 [Note] Binlog end 2016-06-23 22:55:16 2218 [Note] InnoDB: FTS optimize thread exiting. 2016-06-23 22:55:16 2218 [Note] InnoDB: Starting shutdown... 2016-06-23 22:55:17 2218 [Note] InnoDB: Shutdown completed; log sequence number 1625977 OK Filling help tables...2016-06-23 22:55:17 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for _timestamp server option (see documentation for more details). 2016-06-23 22:55:17 2241 [Note] InnoDB: The InnoDB memory heap is disabled 2016-06-23 22:55:17 2241 [Note] InnoDB: Mutexes and rw_locks use InnoDB's own implementation 2016-06-23 22:55:17 2241 [Note] InnoDB: Compressed tables use zlib 1.2.3 2016-06-23 22:55:17 2241 [Note] InnoDB: CPU does not support crc32 instructions 2016-06-23 22:55:17 2241 [Note] InnoDB: Using Linux native AIO 2016-06-23 22:55:17 2241 [Note] InnoDB: Initializing buffer pool, size = 128.0M 2016-06-23 22:55:17 2241 [Note] InnoDB: Completed initialization of buffer pool 2016-06-23 22:55:17 2241 [Note] InnoDB: Highest supported file format is Barracuda. 2016-06-23 22:55:17 2241 [Note] InnoDB: 128 rollback segment(s) are active. 2016-06-23 22:55:17 2241 [Note] InnoDB: Waiting for purge to start 2016-06-23 22:55:17 2241 [Note] InnoDB: 1.2.10 started; log sequence number 1625977 2016-06-23 22:55:18 2241 [Note] Binlog end 2016-06-23 22:55:18 2241 [Note] InnoDB: FTS optimize thread exiting. 2016-06-23 22:55:18 2241 [Note] InnoDB: Starting shutdown... 2016-06-23 22:55:19 2241 [Note] InnoDB: Shutdown completed; log sequence number 1625987 OK To start mysqld at boot time you have to copy support-files/mysql.server to the right place for your system PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! To do so, start the server, then issue the following commands: ./bin/mysqladmin -u root password 'new-password' ./bin/mysqladmin -u root -h slave.magedu.com password 'new-password' Alternatively you can run: ./bin/mysql_secure_installation which will also give you the option of removing the test databases and anonymous user created by default. This is strongly recommended for production servers. See the manual for more instructions. You can start the MySQL daemon with: cd . ; ./bin/mysqld_safe & You can test the MySQL daemon with mysql-test-run.pl cd mysql-test ; perl mysql-test-run.pl Please report any problems with the ./bin/mysqlbug script! The latest information about MySQL is available on the web at http://www.mysql.com Support MySQL by buying support/licenses at http://shop.mysql.com New default config file was created as ./my.cnf and will be used by default by the server when you start it. You may edit this file to change server settings WARNING: Default config file /etc/my.cnf exists on the system This file will be read by default by the MySQL server If you do not want to use this, either remove it, or use the --defaults-file argument to mysqld_safe when starting the server [root@slave mysql]# ls（查看当前目录文件及子目录） bin COPYING data docs include INSTALL-BINARY lib man my.cnf mysql-test README scripts share sql-bench support-files [root@slave mysql]# vim my.cnf（编辑mysql配置文件my.cnf） datadir= /mydata/data log-bin = master-bin server-id = 11 socket = /tmp/mysql.sock [root@slave mysql]# cp support-files/mysql.server /etc/init.d/mysqld（复制mysql服务脚本到/etc/init.d/叫mysqld） [root@slave mysql]# chkconfig --add mysqld（将mysqld添加到服务列表） [root@slave mysql]# service mysqld start（启动mysqld服务） Starting MySQL.. [确定] [root@slave mysql]# netstat -tnlp（查看系统服务，-t代表tcp，-n以数字显示，-l监听端口，-p显示服务名称） Active Internet connections (only servers) 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 1046/sshd tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN 1122/master tcp 0 0 127.0.0.1:6010 0.0.0.0:* LISTEN 2188/sshd tcp 0 0 :::3306 :::* LISTEN 2529/mysqld tcp 0 0 :::22 :::* LISTEN 1046/sshd tcp 0 0 ::1:25 :::* LISTEN 1122/master tcp 0 0 ::1:6010 :::* LISTEN 2188/sshd [root@slave mysql]# ls /mydata/data/（查看/mydata/data目录文件及子目录） auto.cnf ibdata1 ib_logfile0 ib_logfile1 master-bin.000001 master-bin.index mysql performance_schema slave.magedu.com.pid test
master:
[root@master mysql]# vim my.cnf（编辑my.cnf配置文件） datadir = /mydata/data innodb_file_per_table = ON server-id = 1 socket = /tmp/mysql.sock log-bin = master-bin binlog-format=ROW log-slave-updates=true gtid-mode=on enforce-gtid-consistency=true master-info-repository=TABLE relay-log-info-repository=TABLE sync-master-info=1 slave-parallel-workers=2 binlog-checksum=CRC32 master-verify-checksum=1 slave-sql-verify-checksum=1 binlog-rows-query-log_events=1 report-port=3306 port=3306 report-host=172.16.100.106 [root@master mysql]# service mysqld restart（重启mysqld服务） Shutting down MySQL.. [确定] Starting MySQL. [确定] [root@master mysql]# mysql（连接mysql） Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.6.10-log MySQL Community Server (GPL) Copyright (c) 2000, 2013, 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 GLOBAL VARIABLES LIKE '%gtid%';（查看mysql全局编辑gtid相关） +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | enforce_gtid_consistency | ON | | gtid_executed | | | gtid_mode | ON | | gtid_owned | | | gtid_purged | | +--------------------------+-------+ 5 rows in set (0.00 sec) mysql> \q（退出mysql） Bye
slave:
[root@slave mysql]# vim my.cnf（编辑my.cnf配置文件） datadir= /mydata/data log-bin = master-bin server-id = 11 socket = /tmp/mysql.sock binlog-format=ROW log-slave-updates=true gtid-mode=on enforce-gtid-consistency=true master-info-repository=TABLE relay-log-info-repository=TABLE sync-master-info=1 slave-parallel-workers=2 binlog-checksum=CRC32 master-verify-checksum=1 slave-sql-verify-checksum=1 binlog-rows-query-log_events=1 report-port=3306 port=3306 report-host=172.16.100.107 提示：从服务器启动不启用二进制日志文件都可以，但是应该启动起来，万一主的挂了，就要把一个从的提升为主的，这时候二进制日志就有用了，所以在这种模型下，如果用到高可用功能可以 启用起来，否则就可以不启用，如果用到HA功能随时有可能提升一个从为主，那么就需要提供二进制日志，否则可以不提供； [root@slave mysql]# service mysqld restart（重启mysqld服务） Shutting down MySQL.. [确定] Starting MySQL. [确定]
master:
[root@master mysql]# mysql（连接mysql数据库） Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.6.10-log MySQL Community Server (GPL) Copyright (c) 2000, 2013, 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 MASTER STATUS;（查看主mysql状态） +-------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +-------------------+----------+--------------+------------------+-------------------+ | master-bin.000003 | 151 | | | | +-------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) 提示：executed_gtid_set已经执行的gtid集合到那了； mysql> SHOW GLOBAL VARIABLES LIKE '%uuid%';（查看全局编辑uuid相关） +---------------+--------------------------------------+ | Variable_name | Value | +---------------+--------------------------------------+ | server_uuid | 83704275-394f-11e6-8892-000c29672c95 | +---------------+--------------------------------------+ 1 row in set (0.00 sec) 提示：在gtid模式下每个server有个uuid，自动生成的，是个随机码；
slave:
[root@slave mysql]# vim /etc/profile.d/mysql.sh（编辑mysql的path环境变量） export PATH=$PATH:/usr/local/mysql/bin [root@slave mysql]# . /etc/profile.d/mysql.sh（读取mysql.sh文件） [root@slave mysql]# mysql（连接mysql服务器） Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.6.10-log MySQL Community Server (GPL) Copyright (c) 2000, 2013, 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 GLOBAL VARIABLES LIKE '%uuid%';（查看mysql全局变量uuid相关） +---------------+--------------------------------------+ | Variable_name | Value | +---------------+--------------------------------------+ | server_uuid | 5192805f-3953-11e6-88ab-000c2980e112 | +---------------+--------------------------------------+ 1 row in set (0.00 sec) 提示：uuid补上事物id就成为gtid了；
master:
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'172.16.%.%' IDENTIFIED BY 'replpass';（授权repluser用户172.16.0.0网络主机备份复制所有库所有表权限 ，用户密码为replpass） Query OK, 0 rows affected (0.00 sec) mysql> FLUSH PRIVILEGES;（刷新授权表） Query OK, 0 rows affected (0.00 sec)
slave:
mysql> CHANGE MASTER TO MASTER_HOST='172.16.100.106',MASTER_USER='repluser',MASTER_PASSWORD='replpass',MASTER_AUTO_POSITION=1;（从主mysql
服务器使用repluser用户密码replpass开始复制二进制文件，MASTER_AUTO_POSITION自动增长）
Query OK, 0 rows affected, 2 warnings (0.10 sec)
mysql> SHOW WARNINGS;（显示警告信息）
+-------+------+------------------------------------------------------------------------------------------------------------------------------
| Level | Code | Message
+-------+------+------------------------------------------------------------------------------------------------------------------------------
| Note | 1759 | Sending passwords in plain text without SSL/TLS is extremely insecure.
| Note | 1760 | Storing MySQL user name or password information in the master.info repository is not secure and is therefore not recommended.
+-------+------+------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------+
|
----------------------------------------------------------------------------------+
|
Please see the MySQL Manual for more about this issue and possible alternatives. |
----------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
提示：密码发送可能是明文的这不安全，保存mysql用户名和密码信息到master.info不安全，因此不是推荐方式，请看mysql官方文档找解决办法；
mysql> SHOW SLAVE STATUS\G（查看从mysql状态）
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 172.16.100.106
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File:
Read_Master_Log_Pos: 4
Relay_Log_File: slave-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File:
Slave_IO_Running: No（slave io没有启动）
Slave_SQL_Running: No（slave 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:
Skip_Counter: 0
Exec_Master_Log_Pos: 0
Relay_Log_Space: 151
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: 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: 0
Master_UUID:
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
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
1 row in set (0.00 sec)
mysql> START SLAVE;（启动从mysql）
Query OK, 0 rows affected, 1 warning (0.04 sec)
mysql> SHOW SLAVE STATUS\G（查看从mysql状态）
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.100.106
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000004
Read_Master_Log_Pos: 577
Relay_Log_File: slave-relay-bin.000005
Relay_Log_Pos: 789
Relay_Master_Log_File: master-bin.000004
Slave_IO_Running: Yes（slave io启动）
Slave_SQL_Running: Yes（slave 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:
Skip_Counter: 0
Exec_Master_Log_Pos: 577
Relay_Log_Space: 1591
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: 83704275-394f-11e6-8892-000c29672c95
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 83704275-394f-11e6-8892-000c29672c95:1-4
Executed_Gtid_Set: 83704275-394f-11e6-8892-000c29672c95:1-4
Auto_Position: 1
1 row in set (0.00 sec)
提示：自动知道读取那个二进制文件；
master:
mysql> CREATE DATABASE mydb;（创建mydb库） Query OK, 1 row affected (0.00 sec)
slave:
mysql> SHOW DATABASES;（显示数据库） +--------------------+ | Database | +--------------------+ | information_schema | | mydb | | mysql | | performance_schema | | test | +--------------------+ 5 rows in set (0.00 sec) 提示：基于gtid的主从复制；
master:
mysql> SHOW SLAVE HOSTS;（显示从主机） +-----------+----------------+------+-----------+--------------------------------------+ | Server_id | Host | Port | Master_id | Slave_UUID | +-----------+----------------+------+-----------+--------------------------------------+ | 11 | 172.16.100.107 | 3306 | 1 | 5192805f-3953-11e6-88ab-000c2980e112 | +-----------+----------------+------+-----------+--------------------------------------+ 1 row in set (0.00 sec) mysql> SHOW GLOBAL STATUS LIKE '%gtid%';（显示和gtid相关的全局状态变量） Empty set (0.00 sec) mysql> SHOW GLOBAL VARIABLES LIKE '%gtid%';（显示全局变量gtid相关） +--------------------------+------------------------------------------+ | Variable_name | Value | +--------------------------+------------------------------------------+ | enforce_gtid_consistency | ON | | gtid_executed | 83704275-394f-11e6-8892-000c29672c95:1-5 |（uuid，1-5分配给每个事物的id号） | gtid_mode | ON | | gtid_owned | | | gtid_purged | | +--------------------------+------------------------------------------+ 5 rows in set (0.00 sec) mysql> SHOW MASTER STATUS;（显示主mysql状态） +-------------------+----------+--------------+------------------+------------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +-------------------+----------+--------------+------------------+------------------------------------------+ | master-bin.000004 | 719 | | | 83704275-394f-11e6-8892-000c29672c95:1-5 | +-------------------+----------+--------------+------------------+------------------------------------------+ 1 row in set (0.00 sec) 提示：Exeuted_Gtid_Set已经执行过的事物的数；
slave:
mysql> SHOW MASTER STATUS;（查看主mysql状态） +-------------------+----------+--------------+------------------+------------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +-------------------+----------+--------------+------------------+------------------------------------------+ | master-bin.000005 | 1065 | | | 83704275-394f-11e6-8892-000c29672c95:1-5 | +-------------------+----------+--------------+------------------+------------------------------------------+ 1 row in set (0.00 sec)
master:
mysql> \q（退出） Bye [root@master mysql]# cd /mydata/data/（切换到/mydata/data目录） [root@master data]# mysqlbinlog master-bin.000001（查看二进制日志文件master-bin.000001内容） /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!40019 SET @@session.max_insert_delayed_threads=0*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #160623 22:33:55 server id 1 end_log_pos 120 CRC32 0xc55db107 Start: binlog v 4, server v 5.6.10-log created 160623 22:33:55 at startup ROLLBACK/*!*/; BINLOG ' U/NrVw8BAAAAdAAAAHgAAAAAAAQANS42LjEwLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAABT82tXEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAQex XcU= '/*!*/; DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/; [root@master data]# mysqlbinlog master-bin.000002（查看master-bin.000002二进制日志文件内容） /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!40019 SET @@session.max_insert_delayed_threads=0*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #160623 22:50:15 server id 1 end_log_pos 120 CRC32 0x448bba8c Start: binlog v 4, server v 5.6.10-log created 160623 22:50:15 at startup ROLLBACK/*!*/; BINLOG ' J/drVw8BAAAAdAAAAHgAAAAAAAQANS42LjEwLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAn92tXEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAYy6 i0Q= '/*!*/; # at 120 #160623 23:08:58 server id 1 end_log_pos 143 CRC32 0xf2f1cf7a Stop DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/; [root@master data]# mysql（连接mysql） Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 5 Server version: 5.6.10-log MySQL Community Server (GPL) Copyright (c) 2000, 2013, 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 GLOBAL VARIABLES LIKE '%commit%';（查看全局变量commit相关） +--------------------------------+-------+ | Variable_name | Value | +--------------------------------+-------+ | autocommit | ON | | binlog_order_commits | ON | | innodb_api_bk_commit_interval | 5 | | innodb_commit_concurrency | 0 | | innodb_flush_log_at_trx_commit | 1 | +--------------------------------+-------+ 5 rows in set (0.00 sec) mysql> SELECT @@autocommit;（查看autocommit变量的值） +--------------+ | @@autocommit | +--------------+ | 1 |（1为自动提交，关闭只有使用commit才会提交事物） +--------------+ 1 row in set (0.00 sec) 提示：默认启用了自动提交，每一个语句都是一个事物，每一个语句都是一个事物这会导致系统性能很差的，建议关闭； mysql> USE mydb;（修改默认库为mydb） Database changed mysql> CREATE TABLE tb1 (id int);（创建表tb1，字段id，整型） Query OK, 0 rows affected (0.06 sec) mysql> \q（退出） Bye [root@master data]# mysqlbinlog master-bin.000004（查看master-bin.000004二进制日志文件内容） /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!40019 SET @@session.max_insert_delayed_threads=0*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #160624 0:11:00 server id 1 end_log_pos 120 CRC32 0x3f6d7b11 Start: binlog v 4, server v 5.6.10-log created 160624 0:11:00 at startup # Warning: this binlog is either in use or was not closed properly. ROLLBACK/*!*/; BINLOG ' FApsVw8BAAAAdAAAAHgAAAABAAQANS42LjEwLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAUCmxXEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAARF7 bT8= '/*!*/; # at 120 #160624 0:11:00 server id 1 end_log_pos 191 CRC32 0x1e1d81ce Previous-GTIDs # 83704275-394f-11e6-8892-000c29672c95:1-2 # at 191 #160624 0:37:24 server id 1 end_log_pos 239 CRC32 0x30171390 GTID [commit=yes] SET @@SESSION.GTID_NEXT= '83704275-394f-11e6-8892-000c29672c95:3'/*!*/; # at 239 #160624 0:37:24 server id 1 end_log_pos 450 CRC32 0x66cc4176 Query thread_id=2 exec_time=0 error_code=0 SET TIMESTAMP=1466699844/*!*/; SET @@session.pseudo_thread_id=2/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; SET @@session.sql_mode=1075838976/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C utf8 *//*!*/; SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'172.16.%.%' IDENTIFIED BY PASSWORD '*D98280F03D0F78162EBDBB9C883FC01395DEA2BF' /*!*/; # at 450 #160624 0:37:48 server id 1 end_log_pos 498 CRC32 0xefab303e GTID [commit=yes] SET @@SESSION.GTID_NEXT= '83704275-394f-11e6-8892-000c29672c95:4'/*!*/; # at 498 #160624 0:37:48 server id 1 end_log_pos 577 CRC32 0x1c9b14af Query thread_id=2 exec_time=0 error_code=0 SET TIMESTAMP=1466699868/*!*/; flush privileges /*!*/; # at 577 #160624 0:55:31 server id 1 end_log_pos 625 CRC32 0x7aef272d GTID [commit=yes] SET @@SESSION.GTID_NEXT= '83704275-394f-11e6-8892-000c29672c95:5'/*!*/; # at 625 #160624 0:55:31 server id 1 end_log_pos 719 CRC32 0xb3e791e3 Query thread_id=4 exec_time=0 error_code=0 SET TIMESTAMP=1466700931/*!*/; CREATE DATABASE mydb /*!*/; # at 719 #160624 1:14:37 server id 1 end_log_pos 767 CRC32 0x5f2dfde5 GTID [commit=yes] SET @@SESSION.GTID_NEXT= '83704275-394f-11e6-8892-000c29672c95:6'/*!*/; # at 767 #160624 1:14:37 server id 1 end_log_pos 866 CRC32 0xba6aefda Query thread_id=5 exec_time=0 error_code=0 use `mydb`/*!*/; SET TIMESTAMP=1466702077/*!*/; CREATE TABLE tb1 (id int) /*!*/; DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/; [root@master data]# mysql（连接mysql） Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 6 Server version: 5.6.10-log MySQL Community Server (GPL) Copyright (c) 2000, 2013, 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 MASTER STATUS;（查看主服务i状态） +-------------------+----------+--------------+------------------+------------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +-------------------+----------+--------------+------------------+------------------------------------------+ | master-bin.000004 | 866 | | | 83704275-394f-11e6-8892-000c29672c95:1-6 | +-------------------+----------+--------------+------------------+------------------------------------------+ 1 row in set (0.00 sec) 提示：执行第6个事物了；
读、写分离
MySQL proxy（lua）
连接路由、Query分析、查询过滤和修改、负载均衡、HA
Amoeba（Java）
查询路由、查询分析、查询过滤、读写分离、负载均衡、
HA
xml()
Cobar（Java）
/etc/mysql.cnf
三、MySQL Proxy
[mysql-proxy]
1、源码安装时，MySQL proxy的依赖关系：
libevent 1.x or higher (1.3b or later is preferred).
lua 5.1.x or higher.
glib2 2.6.0 or higher.
pkg-config.
libtool 1.5 or higher.
MySQL 5.0.x or higher developer files.
2、安装
# tar zxf mysql-proxy-0.8.2.tar.gz
# cd mysql-proxy-0.8.2
# ./configure
# make
# make check
如果管理员有密码，上面的步骤则需要使用如下格式进行：
# MYSQL_PASSWORD=root_pwd make check
# make install
默认情况下， mysql-proxy安装在/usr/local/sbin/mysql-proxy，而Lua示例脚本安装在/usr/local/share目录中。
3、配置指令
mysql proxy的各配置参数请参见官方文档，http://dev.mysql.com/doc/refman/5.6/en/mysql-proxy-configuration.html
使用rpm包在rhel6上安装mysql-proxy-0.8.2，其会提供配置文件及服务脚本，但没有提供读写分享的脚本。
/etc/sysconfig/mysql-proxy文件用于定义mysql-proxy的启动参数。
ADMIN_USER – the user for the proxy's admin interface. You can leave the default admin user.
ADMIN_PASSWORD – the password for the admin user in clear text. Change the default password for better security.
ADMIN_LUA_SCRIPT – the admin script in the Lua programming language. Without this script the admin interface cannot work. You can leave the default value.
PROXY_USER – the system user under which the proxy will work. By default it is mysql-proxy, and it's safe to leave it as is.
PROXY_OPTIONS – proxy options such as logging level, plugins, and Lua scripts to be loaded.
其中PROXY_OPTIONS是最常用的一个选项，用于定义mysql-proxy工作时的重要参数，例如：
PROXY_OPTIONS="--daemon --log-level=info --log-use-syslog --plugins=proxy --plugins=admin --proxy-backend-addresses=192.168.1.102:3306 --proxy-read-only-backend-addresses=192.168.1.105:3306 --proxy-lua-script=/usr/lib/mysql-proxy/lua/proxy/rw-splitting.lua"
四、安装配置mysql-proxy:
4.1 下载所需要的版本，这里的系统平台为rhel6.4 32位系统，因此就以mysql-proxy-0.8.3-linux-glibc2.3-x86-32bit.tar.gz为例。
# tar xf mysql-proxy-0.8.3-linux-glibc2.3-x86-32bit.tar.gz -C /usr/local
# cd /usr/local
# ln -sv mysql-proxy-0.8.3-linux-glibc2.3-x86-32bit mysql-proxy
添加代理用户
# useradd mysql-proxy
4.2 为mysql-proxy提供SysV服务脚本，内容如下所示
#!/bin/bash
#
# mysql-proxy This script starts and stops the mysql-proxy daemon
#
# chkconfig: - 78 30
# processname: mysql-proxy
# description: mysql-proxy is a proxy daemon for mysql
# Source function library.
. /etc/rc.d/init.d/functions
prog="/usr/local/mysql-proxy/bin/mysql-proxy"
# Source networking configuration.
if [ -f /etc/sysconfig/network ]; then
. /etc/sysconfig/network
fi
# Check that networking is up.
[ ${NETWORKING} = "no" ] && exit 0
# Set default mysql-proxy configuration.
ADMIN_USER="admin"
ADMIN_PASSWD="admin"
ADMIN_LUA_SCRIPT="/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua"
PROXY_OPTIONS="--daemon"
PROXY_PID=/var/run/mysql-proxy.pid
PROXY_USER="mysql-proxy"
# Source mysql-proxy configuration.
if [ -f /etc/sysconfig/mysql-proxy ]; then
. /etc/sysconfig/mysql-proxy
fi
RETVAL=0
start() {
echo -n $"Starting $prog: "
daemon $prog $PROXY_OPTIONS --pid-file=$PROXY_PID --proxy-address="$PROXY_ADDRESS" --user=$PROXY_USER --admin-username="$ADMIN_USER" --admin-lua-script="$ADMIN_LUA_SCRIPT" --admin-password="$ADMIN_PASSWORD"
RETVAL=$?
echo
if [ $RETVAL -eq 0 ]; then
touch /var/lock/subsys/mysql-proxy
fi
}
stop() {
echo -n $"Stopping $prog: "
killproc -p $PROXY_PID -d 3 $prog
RETVAL=$?
echo
if [ $RETVAL -eq 0 ]; then
rm -f /var/lock/subsys/mysql-proxy
rm -f $PROXY_PID
fi
}
# See how we were called.
case "$1" in
start)
start
;;
stop)
stop
;;
restart)
stop
start
;;
condrestart|try-restart)
if status -p $PROXY_PIDFILE $prog >&/dev/null; then
stop
start
fi
;;
status)
status -p $PROXY_PID $prog
;;
*)
echo "Usage: $0 {start|stop|restart|reload|status|condrestart|try-restart}"
RETVAL=1
;;
esac
exit $RETVAL
将上述内容保存为/etc/rc.d/init.d/mysql-proxy，给予执行权限，而后加入到服务列表。
# chmod +x /etc/rc.d/init.d/mysql-proxy
# chkconfig --add mysql-proxy
4.3 为服务脚本提供配置文件/etc/sysconfig/mysql-proxy，内容如下所示：
# Options for mysql-proxy
ADMIN_USER="admin"
ADMIN_PASSWORD="admin"
ADMIN_ADDRESS=""
ADMIN_LUA_SCRIPT="/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua"
PROXY_ADDRESS=""
PROXY_USER="mysql-proxy"
PROXY_OPTIONS="--daemon --log-level=info --log-use-syslog"
其中最后一行，需要按实际场景进行修改，例如：
PROXY_OPTIONS="--daemon --log-level=info --log-use-syslog --plugins=proxy --plugins=admin --proxy-backend-addresses=172.16.100.6:3306 --proxy-read-only-backend-addresses=172.16.100.7:3306 --proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua"
其中的proxy-backend-addresses选项和proxy-read-only-backend-addresses选项均可重复使用多次，以实现指定多个读写服务器或只读服务器。
4.4 mysql-proxy的配置选项
mysql-proxy的配置选项大致可分为帮助选项、管理选项、代理选项及应用程序选项几类，下面一起去介绍它们。
--help
--help-admin
--help-proxy
--help-all ———— 以上四个选项均用于获取帮助信息；
--proxy-address=host:port ———— 代理服务监听的地址和端口；
--admin-address=host:port ———— 管理模块监听的地址和端口；
--proxy-backend-addresses=host:port ———— 后端mysql服务器的地址和端口；
--proxy-read-only-backend-addresses=host:port ———— 后端只读mysql服务器的地址和端口；
--proxy-lua-script=file_name ———— 完成mysql代理功能的Lua脚本；
--daemon ———— 以守护进程模式启动mysql-proxy；
--keepalive ———— 在mysql-proxy崩溃时尝试重启之；
--log-file=/path/to/log_file_name ———— 日志文件名称；
--log-level=level ———— 日志级别；
--log-use-syslog ———— 基于syslog记录日志；
--plugins=plugin,.. ———— 在mysql-proxy启动时加载的插件；
--user=user_name ———— 运行mysql-proxy进程的用户；
--defaults-file=/path/to/conf_file_name ———— 默认使用的配置文件路径；其配置段使用[mysql-proxy]标识；
--proxy-skip-profiling ———— 禁用profile；
--pid-file=/path/to/pid_file_name ———— 进程文件名；
5、复制如下内容建立admin.lua文件，将其保存至/usr/local/mysql-proxy/share/doc/mysql-proxy/目录中。
--[[ $%BEGINLICENSE%$
Copyright (c) 2007, 2012, Oracle and/or its affiliates. All rights reserved.
This program is free software; you can redistribute it and/or
modify it under the terms of the GNU General Public License as
published by the Free Software Foundation; version 2 of the
License.
This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
GNU General Public License for more details.
You should have received a copy of the GNU General Public License
along with this program; if not, write to the Free Software
Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA
02110-1301 USA
$%ENDLICENSE%$ --]]
function set_error(errmsg)
proxy.response = {
type = proxy.MYSQLD_PACKET_ERR,
errmsg = errmsg or "error"
}
end
function read_query(packet)
if packet:byte() ~= proxy.COM_QUERY then
set_error("[admin] we only handle text-based queries (COM_QUERY)")
return proxy.PROXY_SEND_RESULT
end
local query = packet:sub(2)
local rows = { }
local fields = { }
if query:lower() == "select * from backends" then
fields = {
{ name = "backend_ndx",
type = proxy.MYSQL_TYPE_LONG },
{ name = "address",
type = proxy.MYSQL_TYPE_STRING },
{ name = "state",
type = proxy.MYSQL_TYPE_STRING },
{ name = "type",
type = proxy.MYSQL_TYPE_STRING },
{ name = "uuid",
type = proxy.MYSQL_TYPE_STRING },
{ name = "connected_clients",
type = proxy.MYSQL_TYPE_LONG },
}
for i = 1, #proxy.global.backends do
local states = {
"unknown",
"up",
"down"
}
local types = {
"unknown",
"rw",
"ro"
}
local b = proxy.global.backends[i]
rows[#rows + 1] = {
i,
b.dst.name, -- configured backend address
states[b.state + 1], -- the C-id is pushed down starting at 0
types[b.type + 1], -- the C-id is pushed down starting at 0
b.uuid, -- the MySQL Server's UUID if it is managed
b.connected_clients -- currently connected clients
}
end
elseif query:lower() == "select * from help" then
fields = {
{ name = "command",
type = proxy.MYSQL_TYPE_STRING },
{ name = "description",
type = proxy.MYSQL_TYPE_STRING },
}
rows[#rows + 1] = { "SELECT * FROM help", "shows this help" }
rows[#rows + 1] = { "SELECT * FROM backends", "lists the backends and their state" }
else
set_error("use 'SELECT * FROM help' to see the supported commands")
return proxy.PROXY_SEND_RESULT
end
proxy.response = {
type = proxy.MYSQLD_PACKET_OK,
resultset = {
fields = fields,
rows = rows
}
}
return proxy.PROXY_SEND_RESULT
end
6、测试
6.1 管理功能测试
# mysql -uadmin -padmin -h172.16.100.107 --port=4041
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.99-agent-admin
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> SELECT * FROM backends;
+-------------+-------------------+-------+------+------+-------------------+
| backend_ndx | address | state | type | uuid | connected_clients |
+-------------+-------------------+-------+------+------+-------------------+
| 1 | 172.16.100.6:3306 | up | rw | NULL | 0 |
| 2 | 172.16.100.7:3306 | up | ro | NULL | 0 |
+-------------+-------------------+-------+------+------+-------------------+
2 rows in set (0.00 sec)
6.2 读写分离测试
# mysql -uroot -pmagedu.com -h172.16.100.107 --port=4040
实验说明：使用172.16.100.201的linux主机作为mysql-proxy，将读请求转发到172.16.100.107，写请求装法到172.16.100.106；
mysql-proxy:
[root@Smoke ~]# ifconfig（查看网卡接口信息）
eth0 Link encap:Ethernet HWaddr 00:0C:29:AE:7D:34
inet addr:172.16.100.201 Bcast:172.16.100.255 Mask:255.255.255.0
inet6 addr: fe80::20c:29ff:feae:7d34/64 Scope:Link
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
RX packets:1190 errors:0 dropped:0 overruns:0 frame:0
TX packets:1139 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:96914 (94.6 KiB) TX bytes:155184 (151.5 KiB)
Interrupt:19 Base address:0x2000
lo Link encap:Local Loopback
inet addr:127.0.0.1 Mask:255.0.0.0
inet6 addr: ::1/128 Scope:Host
UP LOOPBACK RUNNING MTU:16436 Metric:1
RX packets:0 errors:0 dropped:0 overruns:0 frame:0
TX packets:0 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:0
RX bytes:0 (0.0 b) TX bytes:0 (0.0 b)
master:
[root@master ~]# ifconfig（查看网卡接口信息）
eth0 Link encap:Ethernet HWaddr 00:0C:29:67:2C:95
inet addr:172.16.100.106 Bcast:172.16.100.255 Mask:255.255.255.0
inet6 addr: fe80::20c:29ff:fe67:2c95/64 Scope:Link
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
RX packets:327552 errors:16 dropped:16 overruns:0 frame:0
TX packets:339005 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:316541351 (301.8 MiB) TX bytes:319709106 (304.8 MiB)
Interrupt:19 Base address:0x2000
lo Link encap:Local Loopback
inet addr:127.0.0.1 Mask:255.0.0.0
inet6 addr: ::1/128 Scope:Host
UP LOOPBACK RUNNING MTU:16436 Metric:1
RX packets:7 errors:0 dropped:0 overruns:0 frame:0
TX packets:7 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:0
RX bytes:712 (712.0 b) TX bytes:712 (712.0 b)
slave:
[root@slave ~]# ifconfig（查看网卡接口信息）
eth0 Link encap:Ethernet HWaddr 00:0C:29:80:E1:12
inet addr:172.16.100.107 Bcast:172.16.100.255 Mask:255.255.255.0
inet6 addr: fe80::20c:29ff:fe80:e112/64 Scope:Link
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
RX packets:228355 errors:1 dropped:1 overruns:0 frame:0
TX packets:119000 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:311554759 (297.1 MiB) TX bytes:9968187 (9.5 MiB)
Interrupt:19 Base address:0x2000
lo Link encap:Local Loopback
inet addr:127.0.0.1 Mask:255.0.0.0
inet6 addr: ::1/128 Scope:Host
UP LOOPBACK RUNNING MTU:16436 Metric:1
RX packets:0 errors:0 dropped:0 overruns:0 frame:0
TX packets:0 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:0
RX bytes:0 (0.0 b) TX bytes:0 (0.0 b)
mysql-proxy:
[root@Smoke ~]# rpm -qa lua（查看是否安装lua软件） lua-5.1.4-4.1.el6.i686 [root@Smoke ~]# lftp 172.16.0.1（连接ftp服务器） lftp 172.16.0.1:/pub/Sources> cd mysql-proxy/（切换到mysql-proxy目录） lftp 172.16.0.1:/pub/Sources/mysql-proxy> get mysql-proxy-0.8.3-linux-glibc2.3-x86-32bit.tar.gz（下载mysql-proxy-0.8.3） 8245793 bytes transferred lftp 172.16.0.1:/pub/Sources/mysql-proxy> bye（退出） [root@Smoke ~]# ls（查看当前目录文件及子目录） anaconda-ks.cfg install.log install.log.syslog mysql-proxy-0.8.3-linux-glibc2.3-x86-32bit.tar.gz [root@Smoke ~]# useradd -r mysql-proxy（添加mysql-proxy用户） [root@Smoke ~]# tar xf mysql-proxy-0.8.3-linux-glibc2.3-x86-32bit.tar.gz -C /usr/local/（解压mysql-proxy到/usr/local目录，x解压，f后面跟文件，-C更改 解压目录） [root@Smoke ~]# cd /usr/local/（切换到/usr/local目录） [root@Smoke local]# ls（查看当前目录文件及子目录） bin etc games include lib libexec mysql-proxy-0.8.3-linux-glibc2.3-x86-32bit sbin share src [root@Smoke local]# ln -sv mysql-proxy-0.8.3-linux-glibc2.3-x86-32bit mysql-proxy（给mysql-proxy-0.8.3创建软连接叫mysql-proxy，-s软连接，-v显示创建 过程） "mysql-proxy" -> "mysql-proxy-0.8.3-linux-glibc2.3-x86-32bit" [root@Smoke local]# ll（查看当前目录文件及子目录详细信息） 总用量 40 drwxr-xr-x. 2 root root 4096 6月 28 2011 bin drwxr-xr-x. 2 root root 4096 6月 28 2011 etc drwxr-xr-x. 2 root root 4096 6月 28 2011 games drwxr-xr-x. 2 root root 4096 6月 28 2011 include drwxr-xr-x. 2 root root 4096 6月 28 2011 lib drwxr-xr-x. 2 root root 4096 6月 28 2011 libexec lrwxrwxrwx. 1 root root 42 6月 23 13:23 mysql-proxy -> mysql-proxy-0.8.3-linux-glibc2.3-x86-32bit drwxr-xr-x. 8 7157 wheel 4096 8月 6 2012 mysql-proxy-0.8.3-linux-glibc2.3-x86-32bit drwxr-xr-x. 2 root root 4096 6月 28 2011 sbin drwxr-xr-x. 5 root root 4096 6月 23 08:08 share drwxr-xr-x. 2 root root 4096 6月 28 2011 src [root@Smoke local]# cd mysql-proxy（切换到mysql-proxy目录） [root@Smoke mysql-proxy]# ls（查看当前目录文件及子目录） bin include lib libexec licenses share [root@Smoke mysql-proxy]# ls bin/（查看bin目录文件及子目录） mysql-binlog-dump mysql-myisam-dump mysql-proxy [root@Smoke mysql-proxy]# vim /etc/profile.d/mysql-proxy.sh（编辑mysql-proxy.sh的PATH环境变量） export PATH=$PATH:/usr/local/mysql-proxy/bin [root@Smoke mysql-proxy]# . /etc/profile.d/mysql-proxy.sh（读取mysql-proxy.sh脚本） [root@Smoke mysql-proxy]# mysql-proxy --help（查看mysql-proxy的帮助） Usage: mysql-proxy [OPTION...] - MySQL Proxy Help Options: -?, --help Show help options（显示帮助） --help-all Show all help options（显示所有帮助） --help-proxy Show options for the proxy-module Application Options: -V, --version Show version --defaults-file=<file> configuration file --verbose-shutdown Always log the exit code when shutting down --daemon Start in daemon-mode --user=<user> Run mysql-proxy as user --basedir=<absolute path> Base directory to prepend to relative paths in the config --pid-file=<file> PID file in case we are started as daemon --plugin-dir=<path> path to the plugins --plugins=<name> plugins to load --log-level=(error|warning|info|message|debug) log all messages of level ... or higher --log-file=<file> log all messages in a file --log-use-syslog log all messages to syslog --log-backtrace-on-crash try to invoke debugger on crash --keepalive try to restart the proxy if it crashed --max-open-files maximum number of open files (ulimit -n) --event-threads number of event-handling threads (default: 1) --lua-path=<...> set the LUA_PATH --lua-cpath=<...> set the LUA_CPATH [root@Smoke mysql-proxy]# mysql-proxy --help-all（查看mysql-proxy的所有帮助） Usage: mysql-proxy [OPTION...] - MySQL Proxy Help Options: -?, --help Show help options --help-all Show all help options --help-proxy Show options for the proxy-module proxy-module（代理相关配置） -P, --proxy-address=<host:port> listening address:port of the proxy-server (default: :4040)（代理服务器工作监听的端口） -r, --proxy-read-only-backend-addresses=<host:port> address:port of the remote slave-server (default: not set)（只读后端） -b, --proxy-backend-addresses=<host:port> address:port of the remote backend-servers (default: 127.0.0.1:3306)（读写后端） --proxy-skip-profiling disables profiling of queries (default: enabled) --proxy-fix-bug-25371 fix bug #25371 (mysqld > 5.1.12) for older libmysql versions -s, --proxy-lua-script=<file> filename of the lua script (default: not set)（为了实现代理功能用到的lua脚本的文件路径） --no-proxy don't start the proxy-module (default: enabled) --proxy-pool-no-change-user don't use CHANGE_USER to reset the connection coming from the pool (default: enabled) --proxy-connect-timeout connect timeout in seconds (default: 2.0 seconds)（连接超时时长） --proxy-read-timeout read timeout in seconds (default: 8 hours)（读超时时长） --proxy-write-timeout write timeout in seconds (default: 8 hours)（写超时时长） Application Options:（mysql应用程序自身相关配置） -V, --version Show version --defaults-file=<file> configuration file（默认读取的配置文件） --verbose-shutdown Always log the exit code when shutting down --daemon Start in daemon-mode（以守护进程模式运行） --user=<user> Run mysql-proxy as user（运行mysql-proxy的用户） --basedir=<absolute path> Base directory to prepend to relative paths in the config（指定mysql-proxy配置文件所在 路径） --pid-file=<file> PID file in case we are started as daemon（进程ID文件路径） --plugin-dir=<path> path to the plugins（把这个目录的插件都加载进来） --plugins=<name> plugins to load（加载额外插件） --log-level=(error|warning|info|message|debug) log all messages of level ... or higher（日志级别） --log-file=<file> log all messages in a file（日志文件） --log-use-syslog log all messages to syslog（使用系统日志文件，发送到message文件里面） --log-backtrace-on-crash try to invoke debugger on crash --keepalive try to restart the proxy if it crashed（如果mysql-proxy宕机了，会尝试重启） --max-open-files maximum number of open files (ulimit -n) --event-threads number of event-handling threads (default: 1)（事件处理器句柄打开几个线程，默认1个） --lua-path=<...> set the LUA_PATH --lua-cpath=<...> set the LUA_CPATH（lua引擎相关路径） [root@Smoke ~]# cd /usr/local/mysql-proxy/（切换到/usr/local/mysql-proxy目录） [root@Smoke mysql-proxy]# ls（查看当前目录文件及子目录） bin include lib libexec licenses share [root@Smoke mysql-proxy]# ls lib（查看lib目录文件及子目录） libevent-1.4.so.2 libglib-2.0.so.0.1600.6 libmysql-chassis-glibext.so.0 libmysql-proxy.so libevent-1.4.so.2.1.3 libgmodule-2.0.so libmysql-chassis-glibext.so.0.0.0 libmysql-proxy.so.0 libevent_core-1.4.so.2 libgmodule-2.0.so.0 libmysql-chassis.la libmysql-proxy.so.0.0.0 libevent_core-1.4.so.2.1.3 libgmodule-2.0.so.0.1600.6 libmysql-chassis.so libpcreposix.so libevent_core.so libgthread-2.0.so libmysql-chassis.so.0 libpcreposix.so.0 libevent_extra-1.4.so.2 libgthread-2.0.so.0 libmysql-chassis.so.0.0.0 libpcreposix.so.0.0.0 libevent_extra-1.4.so.2.1.3 libgthread-2.0.so.0.1600.6 libmysql-chassis-timing.la libpcre.so libevent_extra.so liblua-5.1.so libmysql-chassis-timing.so libpcre.so.0 libevent.so liblua.so libmysql-chassis-timing.so.0 libpcre.so.0.0.1 libglib-2.0.so libmysql-chassis-glibext.la libmysql-chassis-timing.so.0.0.0 mysql-proxy libglib-2.0.so.0 libmysql-chassis-glibext.so libmysql-proxy.la pkgconfig [root@Smoke mysql-proxy]# ls lib/mysql-proxy/（查看lib/mysql-proxy目录文件及子目录） lua plugins [root@Smoke mysql-proxy]# ls lib/mysql-proxy/plugins/（查看lib/mysql-proxy/plugins目录文件及子目录） libadmin.la libadmin.so（是mysql-proxy提供的管理接口） libdebug.la libdebug.so libproxy.la libproxy.so libreplicant.la libreplicant.so [root@Smoke mysql-proxy]# mysql-proxy --daemon --log-level=debug --log-file=/var/log/mysql-proxy.log --plugins="admin" --admin-user="admin" --admin-password="admin" --proxy-backend-addresses="172.16.100.106:3306" --proxy-read-only-backend-addresses="172.16.100.107:3306" （启动m ysql-proxy，--daemon以守护进错模式运行，--log-level=debug日志级别，--log-file=/var/log/mysql-proxy.log日志文件路径，--plugins="admin"指定管理插件，-- admin-user="admin"管理帐号，--admin-password="admin"管理密码，--proxy-backend-addresses="172.16.100.106:3306"读写后端mysql服务器，--proxy-read-on ly-backend-addresses="172.16.100.107:3306"只读后端mysql服务器） [root@Smoke mysql-proxy]# tail /var/log/mysql-proxy.log（查看mysql-proxy.log日志内容） 2016-06-23 13:51:23: (critical) mysql-proxy-cli.c:503: Unknown option --admin-user=admin (use --help to show all options)（未知选项--admin-user） 2016-06-23 13:51:23: (message) Initiating shutdown, requested from mysql-proxy-cli.c:513 2016-06-23 13:51:23: (message) shutting down normally, exit code is: 1 [root@Smoke mysql-proxy]# mysql-proxy --daemon --log-level=debug --log-file=/var/log/mysql-proxy.log --plugins="admin" --proxy-backend-addr esses="172.16.100.106:3306" --proxy-read-only-backend-addresses="172.16.100.107:3306" （启动mysql-proxy，--daemon以守护进程模式运行，--log-level =debug日志级别，--log-file=/var/log/mysql-proxy.log日志文件路径，--plugins="admin"指定管理插件，--proxy-backend-addresses="172.16.100.106:3306"读写 后端mysql服务器，--proxy-read-only-backend-addresses="172.16.100.107:3306"只读后端mysql服务器） [root@Smoke mysql-proxy]# tail /var/log/mysql-proxy.log（查看mysql-proxy.log日志文件内容后10行） 2016-06-23 13:51:23: (critical) mysql-proxy-cli.c:503: Unknown option --admin-user=admin (use --help to show all options) 2016-06-23 13:51:23: (message) Initiating shutdown, requested from mysql-proxy-cli.c:513 2016-06-23 13:51:23: (message) shutting down normally, exit code is: 1 2016-06-23 13:58:31: (critical) mysql-proxy-cli.c:503: Unknown option --proxy-backend-addresses=172.16.100.106:3306 (use --help to show all options)（未知选项） 2016-06-23 13:58:31: (message) Initiating shutdown, requested from mysql-proxy-cli.c:513 2016-06-23 13:58:31: (message) shutting down normally, exit code is: 1 [root@Smoke mysql-proxy]# mysql-proxy --daemon --log-level=debug --log-file=/var/log/mysql-proxy.log --plugins="proxy" --proxy-backend-addre sses="172.16.100.106:3306" --proxy-read-only-backend-addresses="172.16.100.107:3306" （启动mysql-proxy，--daemon以守护进程模式运行，--log-level= debug日志级别，--log-file=/var/log/mysql-proxy.log日志文件路径，--plugins="proxy"指定代理插件，--proxy-backend-addresses="172.16.100.106:3306"读写 后端mysql服务器，--proxy-read-only-backend-addresses="172.16.100.107:3306"只读后端mysql服务器） [root@Smoke mysql-proxy]# tail /var/log/mysql-proxy.log（查看mysql-proxy.log日志文件后10行） 2016-06-23 13:51:23: (message) Initiating shutdown, requested from mysql-proxy-cli.c:513 2016-06-23 13:51:23: (message) shutting down normally, exit code is: 1 2016-06-23 13:58:31: (critical) mysql-proxy-cli.c:503: Unknown option --proxy-backend-addresses=172.16.100.106:3306 (use --help to show all options) 2016-06-23 13:58:31: (message) Initiating shutdown, requested from mysql-proxy-cli.c:513 2016-06-23 13:58:31: (message) shutting down normally, exit code is: 1 2016-06-23 14:02:57: (critical) plugin proxy 0.8.3 started 2016-06-23 14:02:57: (debug) max open file-descriptors = 1024 2016-06-23 14:02:57: (message) proxy listening on port :4040（mysql-proxy监听在4040端口） 2016-06-23 14:02:57: (message) added read/write backend: 172.16.100.106:3306 2016-06-23 14:02:57: (message) added read-only backend: 172.16.100.107:3306 [root@Smoke mysql-proxy]# netstat -tnlp（查看系统服务，-t代表tcp，-n以数字显示，-l监听端口，-p显示服务名称） Active Internet connections (only servers) Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name tcp 0 0 0.0.0.0:4040 0.0.0.0:* LISTEN 1801/mysql-proxy tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 1059/sshd tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN 1137/master tcp 0 0 127.0.0.1:6010 0.0.0.0:* LISTEN 1654/sshd tcp 0 0 127.0.0.1:6011 0.0.0.0:* LISTEN 1740/sshd tcp 0 0 :::22 :::* LISTEN 1059/sshd tcp 0 0 ::1:25 :::* LISTEN 1137/master tcp 0 0 ::1:6010 :::* LISTEN 1654/sshd tcp 0 0 ::1:6011 :::* LISTEN 1740/sshd 提示：mysql-proxy监听在4040端口；
slave:
[root@slave ~]# mysql -uroot -p -h172.16.100.201 --port=4040（连接mysql-proxy，-u指定用户，-p指定密码，-h指定主机，--port指定端口） Enter password: ERROR 2003 (HY000): Can't connect to MySQL server on '172.16.100.201' (113)
master:
[root@master ~]# mysql（连接mysql数据库） Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 Server version: 5.6.10-log MySQL Community Server (GPL) Copyright (c) 2000, 2013, 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> GRANT ALL ON *.* TO 'root'@'%' IDENTIFIED BY 'redhat';（授权root用户通过任何主机对所有库所有不所有权限，密码为redhat） Query OK, 0 rows affected (0.00 sec) mysql> FLUSH PRIVILEGES;（刷新授权表） Query OK, 0 rows affected (0.00 sec)
slave:
[root@slave ~]# mysql -uroot -p -h172.16.100.201 --port=4040（连接mysql-proxy，-u指定用户，-p指定密码，-h指定主机，--port指定端口） Enter password: ERROR 2003 (HY000): Can't connect to MySQL server on '172.16.100.201' (113)
master:
mysql> \q（退出mysql） Bye [root@master ~]# mysql -uroot -h172.16.100.106 -p（连接mysql-proxy，-u指定用户，-p指定密码，-h指定主机，--port指定端口） Enter password: ERROR 1045 (28000): Access denied for user 'root'@'master.magedu.com' (using password: YES) [root@master ~]# mysql（连接mysql） Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 Server version: 5.6.10-log MySQL Community Server (GPL) Copyright (c) 2000, 2013, 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> GRANT ALL ON *.* TO 'root'@'172.16.%.%' IDENTIFIED BY 'redhat';（授权root用户通过任何主机对所有库所有不所有权限，密码为redhat） Query OK, 0 rows affected (0.00 sec) mysql> FLUSH PRIVILEGES;（刷新授权表） Query OK, 0 rows affected (0.00 sec) mysql> \q（退出mysql数据库） Bye [root@master ~]# mysql -uroot -h172.16.100.106 -p（连接mysql-proxy，-u指定用户，-p指定密码，-h指定主机，--port指定端口） Enter password: ERROR 1045 (28000): Access denied for user 'root'@'master.magedu.com' (using password: YES) [root@master ~]# mysql（连接mysql） Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 11 Server version: 5.6.10-log MySQL Community Server (GPL) Copyright (c) 2000, 2013, 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 GLOBAL VARIABLES LIKE '%dns%';（显示全局变量dns相关） Empty set (0.00 sec) mysql> SHOW GLOBAL VARIABLES LIKE '%hostname%';（显示全局变量hostname相关） +---------------+-------------------+ | Variable_name | Value | +---------------+-------------------+ | hostname | master.magedu.com | +---------------+-------------------+ 1 row in set (0.00 sec) mysql> SHOW GLOBAL VARIABLES LIKE '%name%';（显示全局变量name相关） +-----------------------------+-------------------------+ | Variable_name | Value | +-----------------------------+-------------------------+ | hostname | master.magedu.com | | innodb_buffer_pool_filename | ib_buffer_pool | | lc_time_names | en_US | | log_bin_basename | /mydata/data/master-bin | | lower_case_table_names | 0 | | relay_log_basename | | | skip_name_resolve | OFF |（跳过名称解析） +-----------------------------+-------------------------+ 7 rows in set (0.00 sec) mysql> SET @@global.skip_name_resolve='ON';（修改全局变量skip_name_resolve值为ON） ERROR 1238 (HY000): Variable 'skip_name_resolve' is a read only variable（只读变量） mysql> \q（退出mysql） Bye
slave:
[root@slave mysql]# mysql -uroot -h172.16.100.106 -p（使用root用户连接172.16.100.106主机的mysql） Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 12 Server version: 5.6.10-log MySQL Community Server (GPL) Copyright (c) 2000, 2013, 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> \q（退出） Bye [root@slave mysql]# mysql -uroot -p -h172.16.100.201 --port=4040（连接mysql-proxy，-u指定用户，-p指定密码，-h指定主机，--port指定端口） Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.6.10-log MySQL Community Server (GPL) Copyright (c) 2000, 2013, 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> CREATE DATABASE hellodb;（创建数据库hellodb） Query OK, 1 row affected (0.00 sec)
master:
[root@master mysql]# mysql（连接mysql数据库） Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.6.10-log MySQL Community Server (GPL) Copyright (c) 2000, 2013, 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 DATABASES;（显示数据库） +--------------------+ | Database | +--------------------+ | information_schema | | hellodb | | mydb | | mysql | | performance_schema | | test | +--------------------+ 6 rows in set (0.00 sec) 提示：hellodb在主数据库上创建，因此路由到主的数据库上来了；
slave:
mysql> SHOW DATABASES;（显示数据库） +--------------------+ | Database | +--------------------+ | information_schema | | hellodb | | mydb | | mysql | | performance_schema | | test | +--------------------+ 6 rows in set (0.08 sec) 提示：从mysql服务器也有从主mysql数据库复制过来的，这可能是碰巧了，因为mysql-proxy并不会实现读写分离的功能，要实现读写分析需要依赖lua脚本；
mysql-proxy:
[root@Smoke ~]# killall mysql-proxy（杀死mysql-proxy进程）
[root@Smoke ~]# mysql-proxy --help-all（查看mysql-proxy的所有帮助）
-s, --proxy-lua-script=<file> filename of the lua script (default: not set)（指定代理脚本是什么）
[root@Smoke ~]# cd /usr/local/mysql-proxy（切换到/usr/local/mysql-proxy目录）
[root@Smoke mysql-proxy]# ls（查看当前目录文件及子目录）
bin include lib libexec licenses share
[root@Smoke mysql-proxy]# cd share/doc/mysql-proxy/（切换到share/doc/mysql-proxy/目录）
[root@Smoke mysql-proxy]# ls
active-queries.lua commit-obfuscator.lua README tutorial-constants.lua tutorial-prep-stmts.lua tutorial-scramble.lua
xtab.lua active-transactions.lua commit-obfuscator.msc ro-balance.lua tutorial-inject.lua
tutorial-query-time.lua tutorial-states.lua admin-sql.lua COPYING ro-pooling.lua tutorial-keepalive.lua
tutorial-resultset.lua tutorial-tokenize.lua analyze-query.lua histogram.lua rw-splitting.lua（读写分离脚本）
tutorial-monitor.lua tutorial-rewrite.lua tutorial-un ion.lua auditing.lua load-multi.lua
tutorial-basic.lua tutorial-packets.lua tutorial-routing.lua tutorial-warnings.lua
提示：必须使用--proxy-lua-script=rw-splitting.lua读写分离脚本，才能读写分离；
[root@Smoke mysql-proxy]# mysql-proxy --daemon --log-level=debug --log-file=/var/log/mysql-proxy.log --plugins="proxy" --proxy-backend-address
es="172.16.100.106:3306" --proxy-read-only-backend-addresses="172.16.100.107:3306" --proxy-lua-script="/usr/local/mysql-proxy/share/doc/mysql-
proxy/rw-splitting.lua"（启动mysql-proxy，--daemon以守护进程模式运行，--log-level=debug日志级别，--log-file=/var/log/mysql-proxy.log日志文件路径，--plu
gins="proxy"指定代理插件，--proxy-backend-addresses="172.16.100.106:3306"读写后端mysql服务器，--proxy-read-only-backend-addresses="172.16.100.107:33
06"只读后端mysql服务器，--proxy-lua-script指定lua脚本）
[root@Smoke mysql-proxy]# tail /var/log/mysql-proxy.log（查看mysql-proxy.log日志文件）
2016-06-24 04:06:45: (critical) mysql-proxy-cli.c:597: Failure from chassis_mainloop. Shutting down.
2016-06-24 04:06:45: (message) Initiating shutdown, requested from mysql-proxy-cli.c:598
2016-06-24 04:06:45: (message) shutting down normally, exit code is: 1
2016-06-24 04:19:38: (message) Initiating shutdown, requested from signal handler
2016-06-24 04:19:38: (message) shutting down normally, exit code is: 0
2016-06-24 04:36:16: (critical) plugin proxy 0.8.3 started
2016-06-24 04:36:16: (debug) max open file-descriptors = 1024（最多只能打开1024个文件）
2016-06-24 04:36:16: (message) proxy listening on port :4040
2016-06-24 04:36:16: (message) added read/write backend: 172.16.100.106:3306
2016-06-24 04:36:16: (message) added read-only backend: 172.16.100.107:3306
[root@Smoke mysql-proxy]# pwd（查看当前所出的路径）
/usr/local/mysql-proxy/share/doc/mysql-proxy
[root@Smoke mysql-proxy]# vim admin.lua（编辑admin.lua脚本）
function set_error(errmsg)
proxy.response = {
type = proxy.MYSQLD_PACKET_ERR,
errmsg = errmsg or "error"
}
end
function read_query(packet)
if packet:byte() ~= proxy.COM_QUERY then
set_error("[admin] we only handle text-based queries (COM_QUERY)")
return proxy.PROXY_SEND_RESULT
end
local query = packet:sub(2)
local rows = { }
local fields = { }
if query:lower() == "select * from backends" then
fields = {
{ name = "backend_ndx",
type = proxy.MYSQL_TYPE_LONG },
{ name = "address",
type = proxy.MYSQL_TYPE_STRING },
{ name = "state",
type = proxy.MYSQL_TYPE_STRING },
{ name = "type",
type = proxy.MYSQL_TYPE_STRING },
{ name = "uuid",
type = proxy.MYSQL_TYPE_STRING },
{ name = "connected_clients",
type = proxy.MYSQL_TYPE_LONG },
}
for i = 1, #proxy.global.backends do
local states = {
"unknown",
"up",
"down"
}
local types = {
"unknown",
"rw",
"ro"
}
local b = proxy.global.backends[i]
rows[#rows + 1] = {
i,
b.dst.name, -- configured backend address
states[b.state + 1], -- the C-id is pushed down starting at 0
types[b.type + 1], -- the C-id is pushed down starting at 0
b.uuid, -- the MySQL Server's UUID if it is managed
b.connected_clients -- currently connected clients
}
end
elseif query:lower() == "select * from help" then
fields = {
{ name = "command",
type = proxy.MYSQL_TYPE_STRING },
{ name = "description",
type = proxy.MYSQL_TYPE_STRING },
}
rows[#rows + 1] = { "SELECT * FROM help", "shows this help" }
rows[#rows + 1] = { "SELECT * FROM backends", "lists the backends and their state" }
else
set_error("use 'SELECT * FROM help' to see the supported commands")
return proxy.PROXY_SEND_RESULT
end
proxy.response = {
type = proxy.MYSQLD_PACKET_OK,
resultset = {
fields = fields,
rows = rows
}
}
return proxy.PROXY_SEND_RESULT
end
[root@Smoke mysql-proxy]# killall mysql-proxy（杀死mysql-proxy脚本）
[root@Smoke mysql-proxy]# mysql-proxy --daemon --log-level=debug --log-file=/var/log/mysql-proxy.log --plugins="proxy" --proxy-backend-addres
ses="172.16.100.106:3306" --proxy-read-only-backend-addresses="172.16.100.107:3306" --proxy-lua-script="/usr/local/mysql-proxy/share/doc/mysq
l-proxy/rw-splitting.lua" --plugins=admin --admin-username="admin" --admin-password="admin" --admin-lua-script="/usr/local/mysql-proxy/share/
doc/mysql-proxy/admin.lua" （启动mysql-proxy，--daemon以守护进程模式运行，--log-level=debug日志级别，--log-file=/var/log/mysql-proxy.log日志文件路径，
--plugins="proxy"指定代理插件，--proxy-backend-addresses="172.16.100.106:3306"读写后端mysql服务器，--proxy-read-only-backend-addresses="172.16.100
.107:3306"只读后端mysql服务器，--proxy-lua-script指定lua脚本，--plugins="admin"指定管理插件，--admin-username="admin"指定管理用户，--admin-password="a
dmin"指定管理密码，--admin-lua-script指定lua脚本）
[root@Smoke mysql-proxy]# tail /var/log/mysql-proxy.log（查看mysql-proxy.log日志文件后10行）
2016-06-24 04:36:16: (message) added read/write backend: 172.16.100.106:3306
2016-06-24 04:36:16: (message) added read-only backend: 172.16.100.107:3306
2016-06-24 04:42:44: (message) Initiating shutdown, requested from signal handler
2016-06-24 04:42:44: (message) shutting down normally, exit code is: 0
2016-06-24 04:48:33: (critical) plugin proxy 0.8.3 started
2016-06-24 04:48:33: (critical) plugin admin 0.8.3 started
2016-06-24 04:48:33: (debug) max open file-descriptors = 1024
2016-06-24 04:48:33: (message) proxy listening on port :4040
2016-06-24 04:48:33: (message) added read/write backend: 172.16.100.106:3306（可读写的mysql为172.16.100.106）
2016-06-24 04:48:33: (message) added read-only backend: 172.16.100.107:3306（只读的mysql为172.16.100.107）
[root@Smoke mysql-proxy]# netstat -tnlp（查看系统服务，-t代表tcp，-n以数字显示，-l监听端口，-p显示服务名称）
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 0.0.0.0:4040 0.0.0.0:* LISTEN 1952/mysql-proxy
tcp 0 0 0.0.0.0:4041 0.0.0.0:* LISTEN 1952/mysql-proxy
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 1059/sshd
tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN 1137/master
tcp 0 0 127.0.0.1:6010 0.0.0.0:* LISTEN 1806/sshd
tcp 0 0 :::22 :::* LISTEN 1059/sshd
tcp 0 0 ::1:25 :::* LISTEN 1137/master
tcp 0 0 ::1:6010 :::* LISTEN 1806/sshd
slave:
[root@slave mysql]# mysql -uadmin -h172.16.100.201 --port=4041 -padmin（连接mysql-proxy，-u指定用户，-h指定主机，--port指定端口，-p指定密码） Warning: Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.0.99-agent-admin（管理代理） Copyright (c) 2000, 2013, 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> SELECT * FROM backends;（查看有多少个后端mysql服务器） +-------------+---------------------+---------+------+------+-------------------+ | backend_ndx | address | state | type | uuid | connected_clients | +-------------+---------------------+---------+------+------+-------------------+ | 1 | 172.16.100.106:3306 | unknown | rw | NULL | 0 | | 2 | 172.16.100.107:3306 | unknown | ro | NULL | 0 | +-------------+---------------------+---------+------+------+-------------------+ 2 rows in set (0.00 sec) 提示：这个管理接口只能使用一个命令，查看有多少个后端mysql服务器，状态unknown； mysql> \q（退出） Bye [root@slave mysql]# mysql -uroot -predhat -h172.16.100.201 --port=4040 -e 'create database magedu.com;'（连接mysql-proxy，-u指定用户，-p指定密码， -h指定主机，--port指定端口，-e指定要执行命令） Warning: Using a password on the command line interface can be insecure. ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.com' at line 1 [root@slave mysql]# mysql -uroot -predhat -h172.16.100.201 --port=4040 -e 'create database mageducom;'（连接mysql-proxy，-u指定用户，-p指定密码， -h指定主机，--port指定端口，-e指定要执行命令） Warning: Using a password on the command line interface can be insecure. 提示：警告这么用密码不安全； [root@slave mysql]# mysql -uadmin -h172.16.100.201 --port=4041 -padmin（连接mysql-proxy，-u指定用户，-h指定主机，--port指定端口，-p指定密码） Warning: Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.0.99-agent-admin Copyright (c) 2000, 2013, 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> SELECT * FROM backends;（查看有多少个后端mysql服务器） +-------------+---------------------+---------+------+------+-------------------+ | backend_ndx | address | state | type | uuid | connected_clients | +-------------+---------------------+---------+------+------+-------------------+ | 1 | 172.16.100.106:3306 | up | rw | NULL | 0 | | 2 | 172.16.100.107:3306 | unknown | ro | NULL | 0 | +-------------+---------------------+---------+------+------+-------------------+ 2 rows in set (0.00 sec) 提示：172.16.100.106:3306的state状态up起来了，说明刚才的写一定是在主服务器执行的； [root@slave ~]# mysql -uroot -p -h172.16.100.201 --port=4040 -e 'select user,password from mysql.user;'（连接mysql-proxy，-u指定用户，-p指定密码， -h指定主机，--port指定端口，-e指定sql语句，查询user和password字段，mysql库中user表） Enter password: +----------+-------------------------------------------+ | user | password | +----------+-------------------------------------------+ | root | | | root | | | root | | | root | | | | | | | | | repluser | *D98280F03D0F78162EBDBB9C883FC01395DEA2BF | | root | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 | | root | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 | +----------+-------------------------------------------+ mysql> mysql> SELECT * FROM backends;（查看有多少个后端mysql服务器） +-------------+---------------------+---------+------+------+-------------------+ | backend_ndx | address | state | type | uuid | connected_clients | +-------------+---------------------+---------+------+------+-------------------+ | 1 | 172.16.100.106:3306 | up | rw | NULL | 0 | | 2 | 172.16.100.107:3306 | unknown | ro | NULL | 0 | +-------------+---------------------+---------+------+------+-------------------+ 2 rows in set (0.00 sec) 提示：从172.16.100.106服务器返回额； [root@slave ~]# mysql -uroot -p -h172.16.100.201 --port=4040 -e 'select user,password from mysql.user;'（连接mysql-proxy，-u指定用户，-p指定密码， -h指定主机，--port指定端口，-e指定sql语句，查询user和password字段，mysql库中user表） Enter password: +----------+-------------------------------------------+ | user | password | +----------+-------------------------------------------+ | root | | | root | | | root | | | root | | | | | | | | | repluser | *D98280F03D0F78162EBDBB9C883FC01395DEA2BF | | root | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 | | root | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 | +----------+-------------------------------------------+ mysql> SELECT * FROM backends;（查看有多少个后端mysql服务器） +-------------+---------------------+---------+------+------+-------------------+ | backend_ndx | address | state | type | uuid | connected_clients | +-------------+---------------------+---------+------+------+-------------------+ | 1 | 172.16.100.106:3306 | up | rw | NULL | 0 | | 2 | 172.16.100.107:3306 | unknown | ro | NULL | 0 | +-------------+---------------------+---------+------+------+-------------------+ 2 rows in set (0.01 sec) 提示：从服务器一直没启起来； [root@slave ~]# mysql -uroot -p -h172.16.100.201 --port=4040 -e 'SHOW TABLES FROM mageducom;'（连接mysql-proxy，-u指定用户，-p指定密码，-h指定主机， --port指定端口，-e指定sql语句，显示mageducom库中的表） Enter password: mysql> SELECT * FROM backends;（查看有多少个后端mysql服务器） +-------------+---------------------+-------+------+------+-------------------+ | backend_ndx | address | state | type | uuid | connected_clients | +-------------+---------------------+-------+------+------+-------------------+ | 1 | 172.16.100.106:3306 | up | rw | NULL | 0 | | 2 | 172.16.100.107:3306 | up | ro | NULL | 0 | +-------------+---------------------+-------+------+------+-------------------+ 2 rows in set (0.00 sec) 提示：从mysql172.16.100.107的state状态up起来，这次查询来自从服务器；
mysql-proxy:
[root@Smoke mysql-proxy]# vim /etc/mysql-proxy.cnf（编辑mysql-proxy.cnf配置文件）
[mysql-proxy]
log-level=debug
log-file=/var/log/mysql-proxy.log
plugins="proxy"
proxy-backend-addresses="172.16.100.106:3306"
proxy-read-only-backend-addresses="172.16.100.107:3306"
proxy-lua-script="/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua
plugins=admin
admin-username="admin"
admin-password="admin"
admin-lua-script="/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua"
[root@Smoke mysql-proxy]# killall mysql-proxy（杀死所有mysql-proxy进程）
[root@Smoke mysql-proxy]# mysql-proxy --help（查看mysql-proxy的命令帮助）
Usage:
mysql-proxy [OPTION...] - MySQL Proxy
Help Options:
-?, --help Show help options
--help-all Show all help options
--help-proxy Show options for the proxy-module
Application Options:
-V, --version Show version
--defaults-file=<file> configuration file（配置文件）
--verbose-shutdown Always log the exit code when shutting down
--daemon Start in daemon-mode
--user=<user> Run mysql-proxy as user
--basedir=<absolute path> Base directory to prepend to relative paths in the config
--pid-file=<file> PID file in case we are started as daemon
--plugin-dir=<path> path to the plugins
--plugins=<name> plugins to load
--log-level=(error|warning|info|message|debug) log all messages of level ... or higher
--log-file=<file> log all messages in a file
--log-use-syslog log all messages to syslog
--log-backtrace-on-crash try to invoke debugger on crash
--keepalive try to restart the proxy if it crashed
--max-open-files maximum number of open files (ulimit -n)
--event-threads number of event-handling threads (default: 1)
--lua-path=<...> set the LUA_PATH
--lua-cpath=<...> set the LUA_CPATH
[root@Smoke mysql-proxy]# mysql-proxy --daemon --defaults-file="/etc/mysql-proxy.cnf"（启动mysql-proxy，--deamon守护进错，--defaults-file指定配置文
件）
2016-06-24 05:22:59: (critical) mysql-proxy-cli.c:326: loading config from '/etc/mysql-proxy.cnf' failed: permissions of /etc/mysql-proxy.cnf a
ren't secure (0660 or stricter required)
2016-06-24 05:22:59: (message) Initiating shutdown, requested from mysql-proxy-cli.c:328
2016-06-24 05:22:59: (message) shutting down normally, exit code is: 1
提示：mysql-proxy.cnf加载配置文件失败，权限应该使用660；
[root@Smoke mysql-proxy]# chmod 660 /etc/mysql-proxy.cnf（将mysql-proxy.cnf权限更改为660）
[root@Smoke mysql-proxy]# mysql-proxy --daemon --defaults-file="/etc/mysql-proxy.cnf"（启动mysql-proxy，--deamon守护进错，--defaults-file指定配置文
件）
[root@Smoke mysql-proxy]# tail /var/log/mysql-proxy.log（查看mysql-proxy.log日志文件后10行）
2016-06-24 05:25:59: (critical) mysql-proxy-cli.c:503: Unknown option --deamon (use --help to show all options)
2016-06-24 05:25:59: (message) Initiating shutdown, requested from mysql-proxy-cli.c:513
2016-06-24 05:25:59: (message) shutting down normally, exit code is: 1
2016-06-24 05:26:50: (debug) chassis-path.c.122: adjusting relative path ("/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua") to base_di
r (/usr/local/mysql-proxy-0.8.3-linux-glibc2.3-x86-32bit). New path: /usr/local/mysql-proxy-0.8.3-linux-glibc2.3-x86-32bit/"/usr/local/mysql-p
roxy/share/doc/mysql-proxy/admin.lua"
2016-06-24 05:26:50: (critical) mysql-proxy-cli.c:503: Unknown option --deamon (use --help to show all options)
2016-06-24 05:26:50: (message) Initiating shutdown, requested from mysql-proxy-cli.c:513
2016-06-24 05:26:50: (message) shutting down normally, exit code is: 1
2016-06-24 05:27:52: (debug) chassis-path.c.122: adjusting relative path ("/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua") to base_di
r (/usr/local/mysql-proxy-0.8.3-linux-glibc2.3-x86-32bit). New path: /usr/local/mysql-proxy-0.8.3-linux-glibc2.3-x86-32bit/"/usr/local/mysql-p
roxy/share/doc/mysql-proxy/admin.lua"
2016-06-24 05:27:52: (critical) plugin admin 0.8.3 started
2016-06-24 05:27:52: (debug) max open file-descriptors = 1024
[root@Smoke mysql-proxy]# netstat -tunlp（查看系统服务，-t代表tcp，-u代表udp，-n以数字显示，-l监听端口，-p显示服务名称）
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 0.0.0.0:4041 0.0.0.0:* LISTEN 2021/mysql-proxy
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 1059/sshd
tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN 1137/master
tcp 0 0 127.0.0.1:6010 0.0.0.0:* LISTEN 1806/sshd
tcp 0 0 :::22 :::* LISTEN 1059/sshd
tcp 0 0 ::1:25 :::* LISTEN 1137/master
tcp 0 0 ::1:6010 :::* LISTEN 1806/sshd
[root@Smoke mysql-proxy]# tail /var/log/mysql-proxy.log（查看mysql-proxy.log日志文件后10行）
2016-06-24 05:26:50: (message) Initiating shutdown, requested from mysql-proxy-cli.c:513
2016-06-24 05:26:50: (message) shutting down normally, exit code is: 1
2016-06-24 05:27:52: (debug) chassis-path.c.122: adjusting relative path ("/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua") to base_di
r (/usr/local/mysql-proxy-0.8.3-linux-glibc2.3-x86-32bit). New path: /usr/local/mysql-proxy-0.8.3-linux-glibc2.3-x86-32bit/"/usr/local/mysql-p
roxy/share/doc/mysql-proxy/admin.lua"
2016-06-24 05:27:52: (critical) plugin admin 0.8.3 started
2016-06-24 05:27:52: (debug) max open file-descriptors = 1024
2016-06-24 05:30:18: (message) Initiating shutdown, requested from signal handler
2016-06-24 05:30:18: (message) shutting down normally, exit code is: 0
2016-06-24 05:30:25: (debug) chassis-path.c.122: adjusting relative path ("/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua") to base_di
r (/usr/local/mysql-proxy-0.8.3-linux-glibc2.3-x86-32bit). New path: /usr/local/mysql-proxy-0.8.3-linux-glibc2.3-x86-32bit/"/usr/local/mysql-p
roxy/share/doc/mysql-proxy/admin.lua"
2016-06-24 05:30:25: (critical) plugin admin 0.8.3 started
2016-06-24 05:30:25: (debug) max open file-descriptors = 1024
[root@Smoke mysql-proxy]# vim /etc/mysql-proxy.cnf（编辑mysql-proxy.cnf脚本）
[mysql-proxy]
log-level=debug
log-file=/var/log/mysql-proxy.log
plugins=proxy
proxy-backend-addresses="172.16.100.106:3306"
proxy-read-only-backend-addresses="172.16.100.107:3306"
proxy-lua-script="/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua
plugins=admin
admin-username="admin"
admin-password="admin"
admin-lua-script="/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua"
[root@Smoke mysql-proxy]# mysql-proxy --help（查看mysql-proxy命令帮助）
Usage:
mysql-proxy [OPTION...] - MySQL Proxy
Help Options:
-?, --help Show help options
--help-all Show all help options
--help-proxy Show options for the proxy-module
Application Options:
-V, --version Show version
--defaults-file=<file> configuration file
--verbose-shutdown Always log the exit code when shutting down
--daemon Start in daemon-mode
--user=<user> Run mysql-proxy as user
--basedir=<absolute path> Base directory to prepend to relative paths in the config
--pid-file=<file> PID file in case we are started as daemon
--plugin-dir=<path> path to the plugins
--plugins=<name> plugins to load
--log-level=(error|warning|info|message|debug) log all messages of level ... or higher
--log-file=<file> log all messages in a file
--log-use-syslog log all messages to syslog
--log-backtrace-on-crash try to invoke debugger on crash
--keepalive try to restart the proxy if it crashed
--max-open-files maximum number of open files (ulimit -n)
--event-threads number of event-handling threads (default: 1)
--lua-path=<...> set the LUA_PATH
--lua-cpath=<...> set the LUA_CPATH
[root@Smoke mysql-proxy]# mysql-proxy --help-all（查看mysql-proxy所有帮助）
Usage:
mysql-proxy [OPTION...] - MySQL Proxy
Help Options:
-?, --help Show help options
--help-all Show all help options
--help-proxy Show options for the proxy-module
proxy-module
-P, --proxy-address=<host:port> listening address:port of the proxy-server (default: :4040)（指定代理的端口和主机）
-r, --proxy-read-only-backend-addresses=<host:port> address:port of the remote slave-server (default: not set)
-b, --proxy-backend-addresses=<host:port> address:port of the remote backend-servers (default: 127.0.0.1:3306)
--proxy-skip-profiling disables profiling of queries (default: enabled)
--proxy-fix-bug-25371 fix bug #25371 (mysqld > 5.1.12) for older libmysql versions
-s, --proxy-lua-script=<file> filename of the lua script (default: not set)
--no-proxy don't start the proxy-module (default: enabled)
--proxy-pool-no-change-user don't use CHANGE_USER to reset the connection coming from the pool (default: enabl
ed)
--proxy-connect-timeout connect timeout in seconds (default: 2.0 seconds)
--proxy-read-timeout read timeout in seconds (default: 8 hours)
--proxy-write-timeout write timeout in seconds (default: 8 hours)
Application Options:
-V, --version Show version
--defaults-file=<file> configuration file
--verbose-shutdown Always log the exit code when shutting down
--daemon Start in daemon-mode
--user=<user> Run mysql-proxy as user
--basedir=<absolute path> Base directory to prepend to relative paths in the config
--pid-file=<file> PID file in case we are started as daemon
--plugin-dir=<path> path to the plugins
--plugins=<name> plugins to load
--log-level=(error|warning|info|message|debug) log all messages of level ... or higher
--log-file=<file> log all messages in a file
--log-use-syslog log all messages to syslog
--log-backtrace-on-crash try to invoke debugger on crash
--keepalive try to restart the proxy if it crashed
--max-open-files maximum number of open files (ulimit -n)
--event-threads number of event-handling threads (default: 1)
--lua-path=<...> set the LUA_PATH
--lua-cpath=<...> set the LUA_CPATH
[root@Smoke mysql-proxy]# vim /etc/mysql-proxy.cnf（编辑mysql-proxy.cnf脚本）
[mysql-proxy]
log-level=debug
log-file=/var/log/mysql-proxy.log
plugins=proxy
proxy-backend-addresses="172.16.100.106:3306"
proxy-read-only-backend-addresses="172.16.100.107:3306"
proxy-lua-script="/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua
plugins=admin
admin-username="admin"
admin-password="admin"
admin-lua-script="/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua"
proxy-address="0.0.0.0:3306"（指定mysql-proxy监听的主机和端口）
[root@Smoke mysql-proxy]# killall mysql-proxy（杀死所有mysql-proxy进错）
[root@Smoke mysql-proxy]# mysql-proxy --daemon --defaults-file="/etc/mysql-proxy.cnf"（启动mysql-proxy，--daemon守护进错，--defaults-file指定配置
文件）
[root@Smoke mysql-proxy]# tail /var/log/mysql-proxy.log（查看mysql-proxy.log日志文件后10行）
2016-06-24 05:30:18: (message) Initiating shutdown, requested from signal handler
2016-06-24 05:30:18: (message) shutting down normally, exit code is: 0
2016-06-24 05:30:25: (debug) chassis-path.c.122: adjusting relative path ("/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua") to base_di
r (/usr/local/mysql-proxy-0.8.3-linux-glibc2.3-x86-32bit). New path: /usr/local/mysql-proxy-0.8.3-linux-glibc2.3-x86-32bit/"/usr/local/mysql-p
roxy/share/doc/mysql-proxy/admin.lua"
2016-06-24 05:30:25: (critical) plugin admin 0.8.3 started
2016-06-24 05:30:25: (debug) max open file-descriptors = 1024
2016-06-24 05:48:39: (message) Initiating shutdown, requested from signal handler
2016-06-24 05:48:40: (message) shutting down normally, exit code is: 0
2016-06-24 05:50:09: (debug) chassis-path.c.122: adjusting relative path ("/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua") to base_di
r (/usr/local/mysql-proxy-0.8.3-linux-glibc2.3-x86-32bit). New path: /usr/local/mysql-proxy-0.8.3-linux-glibc2.3-x86-32bit/"/usr/local/mysql-p
roxy/share/doc/mysql-proxy/admin.lua"
2016-06-24 05:50:09: (critical) plugin admin 0.8.3 started
2016-06-24 05:50:09: (debug) max open file-descriptors = 1024
[root@Smoke mysql-proxy]# netstat -tnlp（查看系统服务，-t代表tcp，-n以数字显示，-l监听端口，-p显示服务名称）
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 0.0.0.0:4041 0.0.0.0:* LISTEN 2071/mysql-proxy
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 1059/sshd
tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN 1137/master
tcp 0 0 127.0.0.1:6010 0.0.0.0:* LISTEN 1806/sshd
tcp 0 0 :::22 :::* LISTEN 1059/sshd
tcp 0 0 ::1:25 :::* LISTEN 1137/master
tcp 0 0 ::1:6010 :::* LISTEN 1806/sshd
[root@Smoke mysql-proxy]# vim /etc/rc.d/init.d/mysql-proxy（编辑mysql-proxy服务脚本）
#!/bin/bash
#
# mysql-proxy This script starts and stops the mysql-proxy daemon
#
# chkconfig: - 78 30
# processname: mysql-proxy
# description: mysql-proxy is a proxy daemon for mysql
# Source function library.
. /etc/rc.d/init.d/functions
prog="/usr/local/mysql-proxy/bin/mysql-proxy"
# Source networking configuration.
if [ -f /etc/sysconfig/network ]; then
. /etc/sysconfig/network
fi
# Check that networking is up.
[ ${NETWORKING} = "no" ] && exit 0
# Set default mysql-proxy configuration.
ADMIN_USER="admin"
ADMIN_PASSWD="admin"
ADMIN_LUA_SCRIPT="/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua"
PROXY_OPTIONS="--daemon"
PROXY_PID=/var/run/mysql-proxy.pid
PROXY_USER="mysql-proxy"
# Source mysql-proxy configuration.
if [ -f /etc/sysconfig/mysql-proxy ]; then
. /etc/sysconfig/mysql-proxy
fi
RETVAL=0
start() {
echo -n $"Starting $prog: "
daemon $prog $PROXY_OPTIONS --pid-file=$PROXY_PID --proxy-address="$PROXY_ADDRESS" --user=$PROXY_USER --admin-username="$ADMIN_USER" --adm
in-lua-script="$ADMIN_LUA_SCRIPT" --admin-password="$ADMIN_PASSWORD"
RETVAL=$?
echo
if [ $RETVAL -eq 0 ]; then
touch /var/lock/subsys/mysql-proxy
fi
}
stop() {
echo -n $"Stopping $prog: "
killproc -p $PROXY_PID -d 3 $prog
RETVAL=$?
echo
if [ $RETVAL -eq 0 ]; then
rm -f /var/lock/subsys/mysql-proxy
rm -f $PROXY_PID
fi
}
# See how we were called.
case "$1" in
start)
start
;;
stop)
stop
;;
restart)
stop
start
;;
condrestart|try-restart)
if status -p $PROXY_PIDFILE $prog >&/dev/null; then
stop
start
fi
;;
status)
status -p $PROXY_PID $prog
;;
*)
echo "Usage: $0 {start|stop|restart|reload|status|condrestart|try-restart}"
RETVAL=1
;;
esac
exit $RETVAL
[root@Smoke mysql-proxy]# chmod +x /etc/rc.d/init.d/mysql-proxy（给mysql-proxy脚本执行权限）
[root@Smoke mysql-proxy]# chkconfig --add mysql-proxy（将mysql-proxy添加为系统服务）
[root@Smoke mysql-proxy]# vim /etc/sysconfig/mysql-proxy（编辑mysql-proxy文件）
# Options for mysql-proxy
ADMIN_USER="admin"（管理帐号）
ADMIN_PASSWORD="admin"（管理员密码）
ADMIN_ADDRESS=""（管理地址，不指定代表所有地址）
ADMIN_LUA_SCRIPT="/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua"（管理脚本路径）
PROXY_ADDRESS=""（代理地址，不指定监听所有地址，默认端口）
PROXY_USER="mysql-proxy"（代理用户）
PROXY_OPTIONS="--daemon --log-level=info --log-file="/var/log/mysql-proxy.log" --plugins=proxy --plugins=admin --proxy-backend-addresses=172.16
.100.106:3306 --proxy-read-only-backend-addresses=172.16.100.107:3306 --proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitt
ing.lua"
[root@Smoke mysql-proxy]# killall mysql-proxy（停止所有的mysql-proxy进程）
[root@Smoke mysql-proxy]# service mysql-proxy start（启动mysql-proxy服务）
正在启动 /usr/local/mysql-proxy/bin/mysql-proxy： [确定]
[root@Smoke mysql-proxy]# netstat -tnlp（显示系统服务，-t代表tcp，-n以数字显示,-l监听端口，-p显示服务名称）
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 0.0.0.0:4040 0.0.0.0:* LISTEN 2223/mysql-proxy
tcp 0 0 0.0.0.0:4041 0.0.0.0:* LISTEN 2223/mysql-proxy
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 1059/sshd
tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN 1137/master
tcp 0 0 127.0.0.1:6010 0.0.0.0:* LISTEN 1806/sshd
tcp 0 0 :::22 :::* LISTEN 1059/sshd
tcp 0 0 ::1:25 :::* LISTEN 1137/master
tcp 0 0 ::1:6010 :::* LISTEN 1806/sshd
slave:
[root@slave ~]# mysql -uroot -p -h172.16.100.201 --port=4040 -e 'SHOW TABLES FROM mageducom;'（连接mysql-proxy，-u指定用户，-指定密码，-h指定主机， --port指定端口，-e指定sql语句，显示mageducom库中表） Enter password: [root@slave ~]# mysql -uroot -p -h172.16.100.201 --port=4040 -e 'select user,password from mysql.user;'（连接mysql-proxy，-u指定用户，-p指定密码， -h指定主机，--port指定端口，-e指定sql语句，显示mysql库user表中user和password字段数据） Enter password: +----------+-------------------------------------------+ | user | password | +----------+-------------------------------------------+ | root | | | root | | | root | | | root | | | | | | | | | repluser | *D98280F03D0F78162EBDBB9C883FC01395DEA2BF | | root | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 | | root | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 | +----------+-------------------------------------------+ [root@slave mysql]# mysql -uadmin -h172.16.100.201 --port=4041 -p（连接mysql代理管理接口，-u指定用户，-h指定主机，--port指定端口，-指定密码） Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.0.99-agent-admin Copyright (c) 2000, 2013, 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> SELECT * FROM backends;（查看有多少个后端mysql服务器） +-------------+---------------------+---------+------+------+-------------------+ | backend_ndx | address | state | type | uuid | connected_clients | +-------------+---------------------+---------+------+------+-------------------+ | 1 | 172.16.100.106:3306 | up | rw | NULL | 0 | | 2 | 172.16.100.107:3306 | unknown | ro | NULL | 0 | +-------------+---------------------+---------+------+------+-------------------+ 2 rows in set (0.00 sec) mysql> \q（退出） Bye
mysql-proxy:
[root@Smoke mysql-proxy]# vim /etc/sysconfig/mysql-proxy（编辑mysql-proxy脚本） # Options for mysql-proxy ADMIN_USER="admin" ADMIN_PASSWORD="admin" ADMIN_ADDRESS="" ADMIN_LUA_SCRIPT="/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua" PROXY_ADDRESS="0.0.0.0:3306"（修改代理工作端口为3306，这样客户端不用指定端口） PROXY_USER="mysql-proxy" PROXY_OPTIONS="--daemon --log-level=info --log-file="/var/log/mysql-proxy.log" --plugins=proxy --plugins=admin --proxy-backend-addresses=172.16 .100.106:3306 --proxy-read-only-backend-addresses=172.16.100.107:3306 --proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitt ing.lua" [root@Smoke mysql-proxy]# service mysql-proxy restart（重启mysql-proxy服务） 停止 /usr/local/mysql-proxy/bin/mysql-proxy： [确定] 正在启动 /usr/local/mysql-proxy/bin/mysql-proxy： [确定] [root@Smoke mysql-proxy]# netstat -tnlp（查看系统服务，-t代表tcp，-n以数字显示，-p显示服务名称） Active Internet connections (only servers) Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name tcp 0 0 0.0.0.0:4041 0.0.0.0:* LISTEN 2249/mysql-proxy tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 2249/mysql-proxy tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 1059/sshd tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN 1137/master tcp 0 0 127.0.0.1:6010 0.0.0.0:* LISTEN 1806/sshd tcp 0 0 :::22 :::* LISTEN 1059/sshd tcp 0 0 ::1:25 :::* LISTEN 1137/master tcp 0 0 ::1:6010 :::* LISTEN 1806/sshd
slave:
[root@slave ~]# mysql -uroot -p -h172.16.100.201 -e 'SHOW TABLES FROM mageducom;'（连接mysql-proxy，-u指定用户，-p指定密码，-h指定主机，-e指定sql命令 ，显示mageducom库中表） Enter password: [root@slave ~]# mysql -uroot -p -h172.16.100.201 -e 'select user,password from mysql.user;'（连接mysql-proxy，-u指定用户，-p指定密码，-h指定主机，-e 指定sql命令，查询mysql库user表user和password字段内容） Enter password: +----------+-------------------------------------------+ | user | password | +----------+-------------------------------------------+ | root | | | root | | | root | | | root | | | | | | | | | repluser | *D98280F03D0F78162EBDBB9C883FC01395DEA2BF | | root | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 | | root | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 | +----------+-------------------------------------------+
浙公网安备 33010602011771号