主从复制配置：
配置过程：
master
(1) 启用二进制日志；
[mysqld]
log_bin=mysql-bin
(2) 设置一个在当前集群中惟一的server-id；
[mysqld]
server_id=#
(3) 创建一个有复制权限(REPLICATION SLAVE, REPLICATION CLIENT)账号；
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO ‘reuser’@’172.16.%.%’ IDENTIFIED BY ‘password’;
FLUSH PRIVILEGES;
slave
(1) 启用中继日志；
[msyqld]
relay_log=relay_log
relay_log_index=relay_log_index
(2) 设置一个在当前集群中惟一的server-id；
[mysqld]
server_id=#
(3) 使用有复制权限用户账号连接至主服务器，并启动复制线程；
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;
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
（4）
一、如果主节点已经运行了一段时间，且有大量数据时，如何配置并启动slave节点？
（1）、备份主服务器
#创建账号
CREATE USER ‘backup’@’localhost’ IDENTIFIED BY ‘backup’;
REVOKE ALL PRIVILEGES, GRANT OPTION FROM ‘backup’;
grant RELOAD,LOCK TABLES,REPLICATION CLIENT,PROCESS,SUPER,CREATE,INSERT,SELECT on *.* to ‘backup’@’localhost’;
FLUSH PRIVILEGES;
#滚动二进制日志
mysqladmin flush-logs -uroot -p
#创建xtrabakcup备份文件
innobackupex –use-memory=4G -H localhost -P 3507 –user=backup –password=backup /backups/
#复制滚动后的二进制日志
MariaDB [(none)]> SHOW MASTER STATUS;
+—————+———-+————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+—————+———-+————–+——————+
| logbin.000037 | 245 | | |
+—————+———-+————–+——————+
1 row in set (0.00 sec)
#查看备份的pos便于二进制日志的恢复
[root@localhost ~]# cat /backups/2017-08-31_17-51-22/xtrabackup_info
tool_command = –use-memory=4G -H localhost -P 3507 –user=backup –password=… /backups/
server_version = 5.5.52-MariaDB
binlog_pos = filename ‘logbin.000037’, position ‘245’
innodb_from_lsn = 0
innodb_to_lsn = 552848350
#导出二进制增量备份sql语句。
# mysqlbinlog –start-position=245 /mariadb/log/logbin.000037 > up.sql
复制起始位置为备份时，二进制文件及其pos；
# scp db00.tar.gz root@192.168.40.129:/root/
# scp -P 4506 up.sql root@192.168.40.129:/root/
（2）、新服务器
##通过备份恢复数据至从服务器
##安装xtrabackup
# wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.8/binary/redhat/6/x86_64/percona-xtrabackup-24-2.4.8-1.el6.x86_64.rpm
# yum -y install percona-xtrabackup-24-2.4.8-1.el6.x86_64.rpm
##编辑新mysql配置文件：
innodb_force_recovery = 0
lower_case_table_names = 1
innodb_file_per_table = 1
#innodb_import_table_from_xtrabackup = 1
slow_query_log_file = /var/lib/mysql/log/mysql-slow.log
slow_query_log = 1
long_query_time = 1 #慢
#skip_name_resolve = YES
max_connect_errors = 6000
open_files_limit = 65535
##更新系统时间
date -s “2017-9-4 15:14:00”
##prepare
# innobackupex –apply-log –redo-only /root/2017-09-04_15-14-16/
##copy back
# innobackupex –copy-back –use-memory=4G /root/2017-09-04_15-14-16/
##修改目录权限
# chown mysql:mysql -R /var/lib/mysql/
##导入二进制日志
mysql> source /root/up.sql
（3）master
(1) 、启用二进制日志；
[mysqld]
log_bin=mysql-bin
(2) 、设置一个在当前集群中惟一的server-id；
[mysqld]
server_id=1
(3) 、创建一个有复制权限(REPLICATION SLAVE, REPLICATION CLIENT)账号；
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO ‘copyuser’@’192.168.%.%’ IDENTIFIED BY ‘copy’;
FLUSH PRIVILEGES;
（4）slave
(1) 启用中继日志；
[msyqld]
relay_log=relay_log
relay_log_index=relay_log_index
(2) 设置一个在当前集群中惟一的server-id；
[mysqld]
server_id=10
(3) 使用有复制权限用户账号连接至主服务器，并启动复制线程；
CHANGE MASTER TO
MASTER_HOST=’192.168.40.135′,
MASTER_USER=’copyuser’,
MASTER_PASSWORD=’copy’,
MASTER_PORT=3306,
MASTER_LOG_FILE=’logbin.000045′,
MASTER_LOG_POS=245,
MASTER_CONNECT_RETRY=10;
(4)启动复制
START SLAVE；
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
（5）查看复制状态
错误：
mysql> SHOW SLAVE STATUS\G
Slave_IO_Running: No
Last_IO_Error: error connecting to master ‘copyuser@192.168.40.135:3306′ – retry-time: 10 retries: 86400
正常：
mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.40.135
Master_User: copyuser
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: logbin.000042
Read_Master_Log_Pos: 494
Relay_Log_File: relay_log.000002
Relay_Log_Pos: 636
Relay_Master_Log_File: logbin.000042
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: 494
Relay_Log_Space: 785
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:
1 row in set (0.00 sec)
如果报错：
主：
MariaDB [(none)]> flush logs;
Query OK, 0 rows affected (0.02 sec)
MariaDB [(none)]> show master status;
+—————+———-+————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+—————+———-+————–+——————+
| logbin.000045 | 245 | | |
+—————+———-+————–+——————+
1 row in set (0.00 sec)
从：
mysql> stop slave
-> ;
Query OK, 0 rows affected (0.00 sec)
mysql> CHANGE MASTER TO
-> MASTER_HOST=’192.168.40.135′,
-> MASTER_USER=’copyuser’,
-> MASTER_PASSWORD=’copy’,
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE=’logbin.000045′,
-> MASTER_LOG_POS=245,
-> MASTER_CONNECT_RETRY=10;
Query OK, 0 rows affected (0.02 sec)
mysql> start slave;
二、保证安全：
（1）slave开启只读锁：
mysql> FLUSH TABLES WITH READ LOCK;
（2）如何保证主从复制时的事务安全？
前提：mysql对二进制日志事件数据会有缓冲; 主服务器被写入立即写入二进制日志
在master上设置如下参数：
sync_binlog = 1
保证事务日志安全：
innodb_flush_log_at_trx_commit=ON 在事务提交时把内存中事务相关的事务立即刷写到磁盘事务日志中
innodb_support_xa=ON innodb是否支持分布式事务
在slave节点：
skip_slave_start=ON 不建议自动启动，保证数据安全
主节点：
sync_master_info = 1
从节点
sync_relay_log = 1 是不是每次写入后是否立即把内存数据写入中继日志
sync_relay_log_info = 1
三、启动半同步复制
主节点：
MariaDB [(none)]> INSTALL PLUGIN rpl_semi_sync_master SONAME ‘semisync_master.so’;
Query OK, 0 rows affected (0.04 sec)
MariaDB [(none)]> SET GLOBAL rpl_semi_sync_master_enabled=1;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> SET GLOBAL rpl_semi_sync_master_timeout=1000;
Query OK, 0 rows affected (0.00 sec)
从节点：
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME ‘semisync_slave.so’;
Query OK, 0 rows affected (0.03 sec)
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE ‘rpl_semi%’;
+————————————+——-+
| Variable_name | Value |
+————————————+——-+
| rpl_semi_sync_master_enabled | ON |
| rpl_semi_sync_master_timeout | 1000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_no_slave | ON |
+————————————+——-+
4 rows in set (0.00 sec)
MariaDB [(none)]> SET GLOBAL rpl_semi_sync_master_enabled = 1;
Query OK, 0 rows affected (0.00 sec)
注意：只需要关闭并启动IO_THREAD即可；
主节点在联系从节点超时后，会自动降纸为异步模式；
在主节点验正是否已经工作于半同步模式：
SHOW GLOBAL STATUS LIKE ‘%semi%’;
四、双主模型
(1) 双方节点都得创建具有复制权限用户；
(2) 双节点都得启用中继日志和二进制日志；
(3) 为保证具有自动增长功能的字段能正确生成ID，需要配置两个节点分别使用偶数或奇数ID号；
(4) 都要把对方配置为自己的主节点；
A节点：
[mysqld]
server-id=1
log-bin = /mariadb/log/logbin
innodb_flush_log_at_trx_commit
innodb_support_xa=ON
sync_binlog = 1
sync_master_info = 1
relay_log=relay_log
relay_log_index=relay_log_index
sync_relay_log = 1 # 是不是每次写入后是否立即把内存数据写入中继日志
sync_relay_log_info = 1
skip_slave_start=ON
auto-increment-increment = 2
auto-increment-offset = 2
CHANGE MASTER TO
MASTER_HOST=’192.168.40.129′,
MASTER_USER=’copyuser’,
MASTER_PASSWORD=’copy’,
MASTER_PORT=3306,
MASTER_LOG_FILE=’mysql-bin.000002′,
MASTER_LOG_POS=1,
MASTER_CONNECT_RETRY=10;
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)
B节点：
[mysqld]
server-id = 10
log-bin=mysql-bin
binlog_format=mixed
innodb_flush_log_at_trx_commit
innodb_support_xa=ON
sync_binlog = 1
sync_master_info = 1
relay_log=relay_log
relay_log_index=relay_log_index
sync_relay_log = 1 # 是不是每次写入后是否立即把内存数据写入中继日志
sync_relay_log_info = 1
skip_slave_start=ON
auto-increment-increment = 2
auto-increment-offset = 2
CHANGE MASTER TO
MASTER_HOST=’192.168.40.135′,
MASTER_USER=’copyuser’,
MASTER_PASSWORD=’copy’,
MASTER_PORT=3306,
MASTER_LOG_FILE=’logbin.000045′,
MASTER_LOG_POS=245,
MASTER_CONNECT_RETRY=10;
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)
五、基于SSL的复制
（1）、安装需要的环境
# yum -y install openssl
MariaDB [(none)]> show variables like ‘%ssl%’;
+—————+———-+
| Variable_name | Value |
+—————+———-+
| have_openssl | DISABLED |
| have_ssl | DISABLED |
DISABLED：为已经编译了ssl模块
NO:为没有ssl模块，需要编译
YES: 为ssl已经开启并可以使用
# ldd `which mysql` | grep ssl
libssl.so.10 => /lib64/libssl.so.10 (0x00007fa5c8daf000)
# 麻烦的是若要安装此缺少的库文件，则需安装libopenssl.1.0.0的程序包，但这个包的安装依赖于glibc2.14版本，而CentOS6.5系统上原生的是glibc2.12版本的，这下就又涉及glibc的升级了，过于麻烦了，而且也很危险，故放弃之
# 那么就只能重新编译安装mariadb10了，从官网下载最新版的源码包安装吧(www.mariadb.com)
（2）、配置ca证书
创建ca：
# vim /etc/hosts
192.168.40.135 node1.zmjcd.cc
192.168.40.129 node2.zmjcd.cc
node1:
# cd /etc/pki/CA/
# (umask 077;openssl genrsa -out private/cakey.pem 2048)
# openssl req -new -x509 -key private/cakey.pem -out cacert.pem -days 3650
Country Name (2 letter code) [XX]:CN
State or Province Name (full name) []:SICHUAN
Locality Name (eg, city) [Default City]:CHENGDU
Organization Name (eg, company) [Default Company Ltd]:ZMJCD
Organizational Unit Name (eg, section) []:CA
Common Name (eg, your name or your server’s hostname) []:node1.zmjcd.cc
Email Address []:adm01@163.com
# (umask 077; openssl genrsa -out master.key 2048)
# openssl req -new -key master.key -out master.csr
Country Name (2 letter code) [XX]:CN
State or Province Name (full name) []:SICHUAN
Locality Name (eg, city) [Default City]:CHENGDU
Organization Name (eg, company) [Default Company Ltd]:ZMJCD
Organizational Unit Name (eg, section) []:MASTER
Common Name (eg, your name or your server’s hostname) []:node1.zmjcd.cc
Email Address []:adm01@163.com
# openssl ca -in /mariadb/ssl/master.csr -out /mariadb/ssl/master.crt
node2:
# (umask 077; openssl genrsa -out slave.key 2048)
# openssl req -new -key slave.key -out slave.csr
Country Name (2 letter code) [XX]:CN
State or Province Name (full name) []:SICHUAN
Locality Name (eg, city) [Default City]:CHENGDU
Organization Name (eg, company) [Default Company Ltd]:ZMJCD
Organizational Unit Name (eg, section) []:SLAVE
Common Name (eg, your name or your server’s hostname) []:node2.zmjcd.cc
Email Address []:adm01@163.com
# scp -P 4506 slave.csr root@node1.zmjcd.cc:/root/
node1:
# openssl ca -in /root/slave.csr -out /root/slave.crt
# scp slave.csr root@node1.zmjcd.cc:/data/ssl/
# scp -P 4506 /etc/pki/CA/cacert.pem root@node2.zmjcd.cc:/data/ssl/
# cp /etc/pki/CA/cacert.pem /mariadb/ssl/
# chown mysql:mysql -R /mariadb/ssl/
（3）配置mariadb ssl复制
node1：
# vim /etc/my.cnf
ssl_ca= /mariadb/ssl/cacert.pem # 配置CA证书
ssl_cert = /mariadb/ssl/master.crt # 配置主库证书
ssl_key = /mariadb/ssl/master.key # 配置主库私钥
ssl_cipher = DHE-RSA-AES256-SHA # 指定支持的加密算法
# systemctl restart mariadb
MariaDB [(none)]> show variables like ‘%ssl%’;
+—————+————————-+
| Variable_name | Value |
+—————+————————-+
| have_openssl | YES |
| have_ssl | YES |
| ssl_ca | /mariadb/ssl/cacert.pem |
| ssl_capath | |
| ssl_cert | /mariadb/ssl/master.crt |
| ssl_cipher | DHE-RSA-AES256-SHA |
| ssl_key | /mariadb/ssl/master.key |
+—————+————————-+
7 rows in set (0.01 sec)
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO ‘sslcopyuser’@’192.168.%.%’ IDENTIFIED BY ‘sslcopy’ require ssl; ## 创建最小权限的复制账号
FLUSH PRIVILEGES;
node2:
# chown mysql:mysql -R /data/ssl
# vim /etc/my.cnf
ssl_ca= /data/ssl/cacert.pem # 配置CA证书
ssl_cert = /data/ssl/slave.crt # 配置主库证书
ssl_key = /data/ssl/slave.key # 配置主库私钥
ssl_cipher = DHE-RSA-AES256-SHA # 指定支持的加密算法
# service mysqld restart
mysql> show variables like ‘%ssl%’;
+—————+———————-+
| Variable_name | Value |
+—————+———————-+
| have_openssl | YES |
| have_ssl | YES |
| ssl_ca | /data/ssl/cacert.pem |
| ssl_capath | |
| ssl_cert | /data/ssl/slave.crt |
| ssl_cipher | DHE-RSA-AES256-SHA |
| ssl_key | /data/ssl/slave.key |
+—————+———————-+
7 rows in set (0.00 sec)
CHANGE MASTER TO
MASTER_HOST=’node1.zmjcd.cc’,
MASTER_USER=’sslcopyuser’,
MASTER_PASSWORD=’sslcopy’,
MASTER_PORT=3306,
MASTER_LOG_FILE=’logbin.000049′,
MASTER_LOG_POS=341,
MASTER_CONNECT_RETRY=10,
MASTER_SSL = 1,
MASTER_SSL_CA = ‘/data/ssl/cacert.pem’,
MASTER_SSL_CERT = ‘/data/ssl/slave.crt’,
MASTER_SSL_KEY = ‘/data/ssl/slave.key’,
MASTER_SSL_CIPHER = ‘DHE-RSA-AES256-SHA’;
MariaDB [(none)]> start slave;
成功：
mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: node1.zmjcd.cc
Master_User: sslcopyuser
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: logbin.000049
Read_Master_Log_Pos: 245
Relay_Log_File: relay_log.000002
Relay_Log_Pos: 526
Relay_Master_Log_File: logbin.000049
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: 245
Relay_Log_Space: 814
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: Yes
Master_SSL_CA_File: /data/ssl/cacert.pem
Master_SSL_CA_Path:
Master_SSL_Cert: /data/ssl/slave.crt
Master_SSL_Cipher: DHE-RSA-AES256-SHA
Master_SSL_Key: /data/ssl/slave.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)
（4）、配置双主
node2：
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO ‘sslcopyuser’@’192.168.%.%’ IDENTIFIED BY ‘sslcopy’ require ssl; ## 创建最小权限的复制账号
FLUSH PRIVILEGES;
node1:
CHANGE MASTER TO
MASTER_HOST=’node2.zmjcd.cc’,
MASTER_USER=’sslcopyuser’,
MASTER_PASSWORD=’sslcopy’,
MASTER_PORT=3306,
MASTER_LOG_FILE=’mysql-bin.000006′,
MASTER_LOG_POS=1064,
MASTER_CONNECT_RETRY=10,
MASTER_SSL = 1,
MASTER_SSL_CA = ‘/mariadb/ssl/cacert.pem’,
MASTER_SSL_CERT = ‘/mariadb/ssl/master.crt’,
MASTER_SSL_KEY = ‘/mariadb/ssl/master.key’,
MASTER_SSL_CIPHER = ‘DHE-RSA-AES256-SHA’;
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: node2.zmjcd.cc
Master_User: sslcopyuser
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: mysql-bin.000006
Read_Master_Log_Pos: 1064
Relay_Log_File: relay_log.000002
Relay_Log_Pos: 529
Relay_Master_Log_File: mysql-bin.000006
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: 1064
Relay_Log_Space: 817
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: Yes
Master_SSL_CA_File: /mariadb/ssl/cacert.pem
Master_SSL_CA_Path:
Master_SSL_Cert: /mariadb/ssl/master.crt
Master_SSL_Cipher: DHE-RSA-AES256-SHA
Master_SSL_Key: /mariadb/ssl/master.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: 10
1 row in set (0.00 sec)
(5)、启动半同步复制
主节点node1：
MariaDB [(none)]> INSTALL PLUGIN rpl_semi_sync_master SONAME ‘semisync_master.so’;
Query OK, 0 rows affected (0.04 sec)
MariaDB [(none)]> SET GLOBAL rpl_semi_sync_master_enabled=1;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> SET GLOBAL rpl_semi_sync_master_timeout=1000;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE ‘rpl_semi%’;
+————————————+——-+
| Variable_name | Value |
+————————————+——-+
| rpl_semi_sync_master_enabled | ON |
| rpl_semi_sync_master_timeout | 1000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_no_slave | ON |
+————————————+——-+
4 rows in set (0.00 sec)
MariaDB [(none)]> INSTALL PLUGIN rpl_semi_sync_slave SONAME ‘semisync_slave.so’;
Query OK, 0 rows affected (0.02 sec)
MariaDB [(none)]> SET GLOBAL rpl_semi_sync_slave_enabled=1;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE ‘rpl_semi%’;
+————————————+——-+
| Variable_name | Value |
+————————————+——-+
| rpl_semi_sync_master_enabled | ON |
| rpl_semi_sync_master_timeout | 1000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_no_slave | ON |
| rpl_semi_sync_slave_enabled | ON |
| rpl_semi_sync_slave_trace_level | 32 |
+————————————+——-+
6 rows in set (0.00 sec)
从节点node2：
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME ‘semisync_slave.so’;
Query OK, 0 rows affected (0.03 sec)
mysql> SET GLOBAL rpl_semi_sync_slave_enabled=1;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW GLOBAL VARIABLES LIKE ‘rpl_semi%’;
+————————————+——-+
| Variable_name | Value |
+————————————+——-+
| rpl_semi_sync_slave_enabled | ON |
| rpl_semi_sync_slave_trace_level | 32 |
+————————————+——-+
6 rows in set (0.00 sec)
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME ‘semisync_master.so’;
Query OK, 0 rows affected (0.07 sec)
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)
mysql> SHOW GLOBAL VARIABLES LIKE ‘rpl_semi%’;
+————————————+——-+
| Variable_name | Value |
+————————————+——-+
| rpl_semi_sync_master_enabled | ON |
| rpl_semi_sync_master_timeout | 1000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_no_slave | ON |
| rpl_semi_sync_slave_enabled | ON |
| rpl_semi_sync_slave_trace_level | 32 |
+————————————+——-+
6 rows in set (0.00 sec)
测试node1：
MariaDB [(none)]> create database test4;
Query OK, 1 row affected (1.03 sec)
MariaDB [(none)]> SHOW GLOBAL STATUS LIKE ‘%semi%’;
+——————————————–+——-+
| Variable_name | Value |
+——————————————–+——-+
| Rpl_semi_sync_master_clients | 0 |
| Rpl_semi_sync_master_net_avg_wait_time | 0 |
| Rpl_semi_sync_master_net_wait_time | 0 |
| Rpl_semi_sync_master_net_waits | 0 |
| Rpl_semi_sync_master_no_times | 1 |
| Rpl_semi_sync_master_no_tx | 1 |
| Rpl_semi_sync_master_status | OFF |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 0 |
| Rpl_semi_sync_master_tx_wait_time | 0 |
| Rpl_semi_sync_master_tx_waits | 0 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 0 |
| Rpl_semi_sync_slave_status | OFF |
+——————————————–+——-+
15 rows in set (0.00 sec)
成功
测试node2：
mysql> create database test5;
Query OK, 1 row affected (1.02 sec)
mysql> SHOW GLOBAL STATUS LIKE ‘%semi%’;
+——————————————–+——-+
| Variable_name | Value |
+——————————————–+——-+
| Rpl_semi_sync_master_clients | 0 |
| Rpl_semi_sync_master_net_avg_wait_time | 0 |
| Rpl_semi_sync_master_net_wait_time | 0 |
| Rpl_semi_sync_master_net_waits | 0 |
| Rpl_semi_sync_master_no_times | 1 |
| Rpl_semi_sync_master_no_tx | 1 |
| Rpl_semi_sync_master_status | OFF |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 0 |
| Rpl_semi_sync_master_tx_wait_time | 0 |
| Rpl_semi_sync_master_tx_waits | 0 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 0 |
| Rpl_semi_sync_slave_status | OFF |
+——————————————–+——-+
15 rows in set (0.00 sec)
附my.cnf
node2:
# cat /etc/my.cnf
[client]
#password = your_password
port = 3306
socket = /tmp/mysql.sock
# Here follows entries for some specific programs
# The MariaDB server
[mysqld]
port = 3306
socket = /tmp/mysql.sock
datadir = /data/mysql
skip-external-locking
key_buffer_size = 256M
max_allowed_packet = 10M
table_open_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 16M
# Try number of CPU’s*2 for thread_concurrency
thread_concurrency = 1
symbolic-links=0
innodb_force_recovery = 0
lower_case_table_names = 1
innodb_file_per_table = 1
innodb_import_table_from_xtrabackup=1
server-id = 10
log-bin=mysql-bin
binlog_format=mixed
innodb_flush_log_at_trx_commit
innodb_support_xa=ON
sync_binlog = 1
sync_master_info = 1
relay_log=relay_log
relay_log_index=relay_log_index
skip_slave_start=ON
sync_relay_log = 1 # 是不是每次写入后是否立即把内存数据写入中继日志
sync_relay_log_info = 1
auto-increment-increment = 2
auto-increment-offset = 2
#ssl
ssl_ca= /data/ssl/cacert.pem # 配置CA证书
ssl_cert = /data/ssl/slave.crt # 配置主库证书
ssl_key = /data/ssl/slave.key # 配置主库私钥
ssl_cipher = DHE-RSA-AES256-SHA # 指定支持的加密算法
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
[myisamchk]
key_buffer_size = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
node1:
[mysqld]
#datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd
datadir = /mariadb/data
log_error = /mariadb/log/mysql-error.log #错误日志路径
slow_query_log_file = /mariadb/log/mysql-slow.log
slow_query_log = 1 #开启慢查询日志
long_query_time = 1 #慢查询超时时间
innodb_force_recovery = 0 #允许覆盖xtarbackup
skip_name_resolve = ON #跳过名称解析，优化操作
max_connect_errors = 6000 #最大连接错误
open_files_limit = 65535 #最大打开文件句柄限制
lower_case_table_names = 1 #忽略大小写
innodb_file_per_table = 1 #每个innodb表单独存放
innodb_import_table_from_xtrabackup = 1 #允许xtrabackup导入数据
server_id=1 #主从复制必须配置且不重复
log-bin = /mariadb/log/logbin #启用二进制日志
innodb_flush_log_at_trx_commit #在事务提交时把内存中事务相关的事务立即刷写到磁盘事务日志中
innodb_support_xa=ON #innodb是否支持分布式事务
sync_binlog = 1 #立即同步二进制日志到磁盘
relay_log=relay_log #启动中继日志，主从复制使用
relay_log_index=relay_log_index #启动中继日志条目
sync_master_info = 1 #立即同步二进制日志
sync_relay_log = 1 #是不是每次写入后是否立即把内存数据写入中继日志
sync_relay_log_info = 1 #是不是每次写入后都理解跟新info文件信息
auto-increment-increment = 2 #每次自动增加多少
auto-increment-offset = 2 #开始自动增加偏移位置
skip_slave_start=ON #不自动启动从节点，保护数据安全
#foreign_key_checks = 0
#foreign_key_checks = no
ssl_ca= /mariadb/ssl/cacert.pem # 配置CA证书
ssl_cert = /mariadb/ssl/master.crt # 配置主库证书
ssl_key = /mariadb/ssl/master.key # 配置主库私钥
ssl_cipher = DHE-RSA-AES256-SHA # 指定支持的加密算法
[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
351
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
