mysql的主从，主主，半同步，SSL复制

chenqb

　　本实验的目的是实现两台主机上的MySQL数据复制，以及基于SSL的复制。
　　*要注意的两点问题所在：
　　1，版本问题：复制双方的版本最好一致，若不同，从节点的版本必须高于主节点的版本
　　2，复制起点问题：（1），从0开始，使用于均为新建服务器。（2），中间开始，就需要完全备份主服务上的数据，并将数据恢复至从服务器； 从服务器从备份时主服务器二进制日志所在位置开始复制。
　　实验环境：
　　node1：MASTER MariaDB 172.16.18.1
　　node2：SALVE    MariaDB 172.16.18.2
　　配置过程：
　　master:
　　1、启用二进制日志
　　2、定义server-id
　　3、创建有复制权限的账号
　　REPLICATION SLAVE, REPLICATION CLIENT

　　MariaDB [(none)]> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO repluser@'172.16.%.%'>　　Query OK, 0 rows affected (0.03 sec)
　　MariaDB [(none)]> FLUSH PRIVILEGES;
　　Query OK, 0 rows affected (0.00 sec)
　　slave:
　　1、启动中继日志
　　2、定义server-id
　　3、使用有复制权限的账号连接master
　　4、启动io thread以及sql thread
　　实验模型一：从零复制MySQL
　　安装MySQL的方法此处不做讲解，不会的可以查看此链接http://aolens.blog.51cto.com/7021142/1539522
　　首先配置node1，主MySQL
　　首先指定自己的二进制日志存放位置。
　　log-bin=/mydata/logs/mysql-bin         #指定二进制日志存放位置
　　mkdir /mydata/logs -pv                 #创建二进制文件存放目录
　　chown -R mysql.mysql /mydata/          #修改logs属主属组为mysql
　　此节点的server-id = 1 不用修改。但要保证从节点可次server-id的不一样。
　　配置node2：从MySQL
　　修改配置文件
　　server-id = 2                              #指定server-id号
　　relay-log = /mydata/relaylogs/relay-log    #指定relay-log的位置以及名称
　　#log-bin=mysql-bin                         #关闭二进制日志。
　　启动node1，node2的MySQL
　　去主MySQL创建有复制权限的账号。node1

　　MariaDB [(none)]> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO>　　Query OK, 0 rows affected (0.24 sec)
　　注意授权账号时要最小权限，最好指定授权给那个IP，不要给出多余的权限。
　　MariaDB [(none)]> FLUSH PRIVILEGES;                            #刷新。
　　Query OK, 0 rows affected (0.04 sec)
　　MariaDB [(none)]> show master status; 查看当前日志和位置。

　　好的，主节点的复制权限账号建立好了，下边去从节点连接主节点。
　　连接主节点的命令CHANGE MASTER TO
　　MariaDB [(none)]> HELP 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                      #如果MySQL修改了端口，此处也要指定
　　| MASTER_CONNECT_RETRY = interval
　　| MASTER_HEARTBEAT_PERIOD = interval
　　| MASTER_LOG_FILE = 'master_log_name'   # 复制起点，主节点上二进制日志，值类型为字串
　　| MASTER_LOG_POS = master_log_pos              # 复制起点，主节点上二进制日志中起始事件的位置，值类型为数值

　　|>
　　|>　　| MASTER_SSL = {0|1}                             #基于SSL复制
　　| MASTER_SSL_CA = 'ca_file_name'                 #CA文件名
　　| MASTER_SSL_CAPATH = 'ca_directory_name'        #CA路径
　　| 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)
　　MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='172.16.18.1',MASTER_USER='relay',MASTER_PASSWORD='relay',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=489;
　　Query OK, 0 rows affected (0.08 sec)
　　查看从节点状态信息：
　　MariaDB [(none)]> show slave status\G
　　*************************** 1. row ***************************
　　Slave_IO_State:
　　Master_Host: 172.16.18.1             #主节点

　　Master_User:>　　Master_Port: 3306                    #端口
　　Connect_Retry: 60                    #多长时间重试一次
　　Master_Log_File: mysql-bin.000001    #当前复制的日志文件
　　Read_Master_Log_Pos: 489             #复制的位置

　　Relay_Log_File:>　　Relay_Log_Pos: 4                     #当前记录的位置
　　Relay_Master_Log_File: mysql-bin.000001
　　Slave_IO_Running: No                 #启动复制线程必须启动为YES
　　Slave_SQL_Running: No                #启动复制线程必须启动为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: 489
　　Relay_Log_Space: 245
　　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
　　1 row in set (0.01 sec)
　　启动Slave_IO_Running和Slave_SQL_Running
　　可以单个线程启动：
　　START SLAVE IO_THREAD;
　　START SLAVE SQL_THREAD;
　　全部启动：
　　START SLAVE;
　　MariaDB [(none)]> show slave status\G

　　Relay_Log_File:>　　Relay_Log_Pos: 529
　　Slave_IO_Running: Yes
　　Slave_SQL_Running: Yes
　　可以看到中继日志在动了。我们现在去master上创建一些数据，看看能不能同步过来。
　　mysql> create database db1;
　　Query OK, 1 row affected (0.00 sec)
　　mysql> use db1
　　Database changed

　　mysql> create table tb1(>　　Query OK, 0 rows affected (0.16 sec）
　　在slave上查看。

　　模型二：MySQL在运行中复制
　　此时你要做的就是先对运行的数据库进程一次全备份
　　mysqldump --all-databases --lock-all-tables  --master-data=2  > /backup/all.sql
　　记录下master-data的位置。
　　发送all.sql到node2
　　当然此时的node2应该是一个新安装的MySQL
　　MySQL < all.sql  即可
　　此时再连接master数据库
　　CHANGE MASTER TO MASTER_HOST='172.16.18.1',MASTER_USER='relay',MASTER_PASSWORD='relay',MASTER_LOG_FILE='mysql-bin.000003',MASTER_LOG_POS=3809;
　　start slave 即可。
　　复制中应该注意的问题：
　　1、如何限制从服务器只读？
　　更改slave的全局服务器变量read-only为YES；
　　注意：此限制对于拥有SUPER权限用户无效；
　　MariaDB [mysql]> SET GLOBAL read_only = 1;   #临时修改，永久修改需要卸载配置文件中mysqld段

　　[mysqld]
　　read_only = 1
　　阻止所有用户执行写操作：
　　MariaDB [mysql]> flush tables with read lock;     #对超级用户同样有效。
　　2、如何保证主从复制时的事务安全？
　　事务提交时会缓存到内存，以减小I/O压力，但是这种情况可能导致主节点挂掉时事务为写入二进制日志，同步不到从节点，导致事务丢失。为了解决这一问题，我们要是他及时同步到磁盘写入二进制日志中国
　　前提：mysql对二进制日志事件数据会有缓冲;
　　在master上设置如下参数：
　　sync_binlog = 1            #开启写入二进制日志

　　3、半同步复制？
　　半同步：master要等待一个从节点把数据完整复制过去；
　　由google贡献的补丁；以插件的方式存在
　　插件位置/usr/local/mysql/lib/plugin/
　　semisync_master.so     #主节点插件
　　semisync_slave.so        #从节点插件
　　在主节点上安装插件。
　　mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
　　Query OK, 0 rows affected (0.16 sec)

　　rpl_semi_sync_master_wait_no_slave   #没有从节点要不要等待，值为ON
　　mysql> SET GLOBAL rpl_semi_sync_master_enabled=1;            #开启模块
　　Query OK, 0 rows affected (0.00 sec)
　　mysql> SET GLOBAL rpl_semi_sync_master_timeout=1000;        #修改时间
　　Query OK, 0 rows affected (0.00 sec)
　　此为临时设置，需要写在配置文件。
　　slave从节点node2
　　MariaDB [(none)]> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so'; #安装模块
　　Query OK, 0 rows affected (0.06 sec)

　　MariaDB [(none)]> set global rpl_semi_sync_slave_enabled = 1; #开启模块
　　Query OK, 0 rows affected (0.00 sec)
　　同样不会永久有效
　　安装模块时需要停止主从，此时启动主从start slave，注意：只关闭并启动IO_THREAD也可；
　　主节点在联系从节点超时后，会自动降纸为异步模式；
　　在主节点查看是否进入半同步。

　　net_avg_wait_time #平均网络等待时间
　　net_wait_time    #网络等待时间
　　net_waits          #网络等待次数
　　no_time            #没有时间
　　no_tx                #没有事务
　　4、复制过滤器
　　让slave仅复制有限的几个数据库，甚至于仅复制某数据库内有限的几张表的机制；
　　有两种方案：
　　1、在主节点上过滤
　　在向二进制日志记录事件时，仅记录指定数据库的相关操作； 这对以后的时间点还原时致命的，不可取。
　　binlog_do_db =                       # 数据库白名单
　　binlog_ignore_db =                  # 数据库黑名单
　　黑名单中有，白名单中也有，黑名单有效。不要黑白名单同时启用
　　2、在从节点上过滤
　　从节点都接收，存在中继日志。仅从中继日志中读取指定的数据库或表的相关事件并应用于本地；
　　replicate_do_db =                    #数据库白名单
　　replicate_ignore_db =               #数据库黑名单
　　replicate_db_table = DB_NAME.TB_NAME  #表白名单
　　replicate_ignore_table =
　　replicate_wild_do_table =          #通配符白名单
　　replicate_wild_ignore_table =
　　5、双主模型复制
　　先stop slave  在主节点创建数据
　　mysql> create database db2;
　　Query OK, 1 row affected (1.02 sec)
　　mysql> use db2
　　Database changed
　　mysql> create table tb1(id int);
　　Query OK, 0 rows affected (0.09 sec)
　　mysql> insert into tb1(id) values (20);
　　在node2中创建数据
　　MariaDB [(none)]> create database db3;
　　Query OK, 1 row affected (0.00 sec)
　　MariaDB [(none)]> use db3
　　Database changed
　　MariaDB [db3]> create table tb1(id int);
　　Query OK, 0 rows affected (0.02 sec)
　　MariaDB [db3]> insert into tb1(id) values (888);
　　Query OK, 1 row affected (0.04 sec)
　　(1) 双方节点都得创建具有复制权限用户；
　　node1上创建用户

　　mysql> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO>　　Query OK, 0 rows affected (0.02 sec)
　　node2上创建用户

　　mysql> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO>　　Query OK, 0 rows affected (0.02 sec)
　　(2) 双节点都得启用中继日志和二进制日志；
　　(3) 为保证具有自动增长功能的字段能正确生成ID，需要配置两个节点分别使用偶数或奇数ID号；
　　node1 为奇数 ，node2选择偶数。
　　(4) 都要把对方配置为自己的主节点；
　　node1节点：
　　[mysqld]
　　server-id=1
　　log-bin=/mydata/logs/master-bin

　　>　　auto-increment-increment = 2
　　auto-increment-offset = 1
　　node2节点：
　　[mysqld]
　　server-id=2
　　log-bin=/mydata/logs/mysql-bin

　　>　　auto-increment-increment = 2
　　auto-increment-offset = 2
　　(5)查看两个节点都到什么位置，在两个节点各自连接，各自启动slave
　　node1：mysql> CHANGE MASTER TO MASTER_HOST='172.16.18.2',MASTER_USER='relay',MASTER_PASSWORD='relay',MASTER_LOG_FILE='mysql-bin.000005',MASTER_LOG_POS=245;
　　Query OK, 0 rows affected (0.13 sec)
　　node2：MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='172.16.18.1',MASTER_USER='relay',MASTER_PASSWORD='relay',MASTER_LOG_FILE='master-bin.000004',MASTER_LOG_POS=245;
　　Query OK, 0 rows affected (0.30 sec)
　　在node1，node2上执行：start slave
　　分别在node1，node2上创建数据，查看是否同步过去了。
　　6、基于SSL的复制
　　（1），在node1上配置CA服务
　　cd /etc/pki/CA/
　　mkdir certs newcerts crl
　　touch index.txt
　　echo 01 > serial
　　生成CA 自签署证书
　　[root@node1 CA]# (umask 077;openssl genrsa -out private/cakey.pem 1024)   #生成密钥
　　Generating RSA private key, 1024 bit long modulus
　　...........................................++++++
　　...............................................................++++++
　　e is 65537 (0x10001)
　　[root@node1 CA]# openssl req -new -x509 -key /etc/pki/CA/private/cakey.pem -out /etc/pki/CA/cacert.pem -days 3655    #自签署
　　[root@node1 CA]# cd /usr/local/mysql/
　　[root@node1 mysql]# mkdir ssl
　　[root@node1 ssl]# （umask 077;openssl genrsa -out /usr/local/mysql/ssl/master.key 2048）   #在ssl目录下生成密钥
　　-bash: （umask: command not found
　　Generating RSA private key, 2048 bit long modulus
　　.....................................+++
　　.................................................................+++
　　e is 65537 (0x10001)
　　[root@node1 ssl]# openssl req -new -key /usr/local/mysql/ssl/master.key -out /usr/local/mysql/ssl/master.csr     #生成证书签署请求
　　[root@node1 ssl]# openssl ca -in master.csr -out master.crt -days 365     #签署证书

　　# vim /etc/my.cnf
　　skip-slave-start=1           #设置重启服务不自动开启线程，需要手动开启
　　ssl-ca=/usr/local/mysql/ssl/cacert.pem
　　ssl-cert=/usr/local/mysql/ssl/master1.crt
　　ssl-key=/usr/local/mysql/ssl/master1.key
　　cp /etc/pki/cacert.pem .     #复制到/usr/local/mysql/ssl/
　　chown -R mysql.mysql ssl
　　创建一个可基于SSL复制的用户。

　　GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO>　　（1），在node2上配置CA服务
　　[root@node2 ssl]# (umask 077;openssl genrsa 1024 > master1slave.key)
　　Generating RSA private key, 1024 bit long modulus
　　.............................++++++
　　..................................................................................++++++
　　e is 65537 (0x10001)
　　[root@node2 ssl]# openssl req -new -key master1slave.key -out master1slave.csr
　　发送 master1slave.csr 去node1签署
　　得到 master1slave.crt发回来，放到/usr/local/mysql/ssl/
　　在Master2上配置Master1的slave信息
　　mysql> CHANGE MASTER TO MASTER_HOST = '172.16.18.2' ,  //指定主服务器
　　-> MASTER_USER = 'relay' ,   //指定用户
　　-> MASTER_PASSWORD = 'relay' ,    //密码
　　-> MASTER_LOG_FILE = 'master-bin.000005' ,  //指定日志
　　-> MASTER_LOG_POS = 245 ,    //指定日志位
　　-> MASTER_SSL = 1 ,
　　-> MASTER_SSL_CA = '/usr/local/mysql/ssl/cacert.pem' ,
　　-> MASTER_SSL_CERT = '/usr/local/mysql/ssl/master1slave.crt' ,
　　-> MASTER_SSL_KEY = '/usr/local/mysql/ssl/master1slave.key';
　　start slave 启动从节点复制。
　　在主节点创建库表，在从节点查看。