网站美工做图推荐,万网科技,建手机网站报价,猎头公司属于什么行业一、MySQL复制相关概念 主从复制#xff1a;主节点将数据同步到多个从节点级联复制#xff1a;主节点将数据同步到一个从节点#xff0c;其他的从节点在向从节点复制数据同步复制#xff1a;将数据从主节点全部同步到从节点时才返回给用户的复制策略叫同步复制异步复制主节点将数据同步到多个从节点级联复制主节点将数据同步到一个从节点其他的从节点在向从节点复制数据同步复制将数据从主节点全部同步到从节点时才返回给用户的复制策略叫同步复制异步复制只要数据写入到主节点就立即返回给用户同步完成读写分离在前端加一个调度器负责将改变数据的语句和查询数据的语句分开调度把写操作调度到主节点读操作调度到从节点 主节点 dump Thread为每个Slave的I/O Thread启动一个dump线程用于向其发送binary log events从节点 I/O Thread向Master请求二进制日志事件并保存于中继日志中SQL Thread从中继日志中读取日志事件在本地完成重放跟复制功能相关的文件 master.info用于保存slave连接至master时的相关信息例如账号、密码、服务器地址等relay-log.info保存在当前slave节点上已经复制的当前二进制日志和本地replay log日志的对应关系复制架构 一主一从一主多从主主复制环状复制级联复制多主一从 常见的架构有主从架构或者级联架构 二、简单的一主一从架构实现 1、新数据库搭建主从架构 1主服务器配置 ~]# vim /etc/my.cnf[mysqld]log_binbinlog_formatROWlog-basenamemaster1server_id1
~]# systemctl restart mariadb
~]# mysql
MariaDB [(none)] GRANT REPLICATION SLAVE ON *.* TO testuser192.168.0.8 IDENTIFIED BY testpass; #授权同步账户
MariaDB [(none)] SHOW MASTER LOGS;
-------------------------------
| Log_name | File_size |
-------------------------------
| master1-bin.000001 | 26756 |
| master1-bin.000002 | 921736 |
| master1-bin.000003 | 401 | #记录此位置从服务器从这里开始同步
------------------------------- 2从服务器配置 ~]# vim /etc/my.cnf[mysqld]server_id2 #服务器ID唯一relay_logrelay-logrelay_log_indexrelay-log.indexread_onlyON
~]# systemctl restart mariadb
~]# mysql
MariaDB [(none)] CHANGE MASTER TO- MASTER_HOST192.168.0.7, #指定主节点IP- MASTER_USERtestuser, #同步用户的用户名- MASTER_PASSWORDtestpass, #密码- MASTER_PORT3306,- MASTER_LOG_FILEmaster1-bin.000003, #以上记录的文件- MASTER_LOG_POS401, #位置- MASTER_CONNECT_RETRY10; #重试时间10秒
MariaDB [(none)] START SLAVE; #开始主从复制 3测试 在主节点上生成一些数据
MariaDB [(none)] CREATE DATABASE testdb;
MariaDB [(none)] use testdb
MariaDB [testdb] create table testlog (id int auto_increment primary key,name char(30),age int default 20);
MariaDB [testdb] delimiter $$
MariaDB [testdb] create procedure pro_testlog()- begin- declare i int;- set i 1;- while i 100000- do insert into testlog(name,age) values (concat(testuser,i),i);- set i i 1; - end while;- end$$
MariaDB [testdb] delimiter ;
MariaDB [testdb] START TRANSACTION;
MariaDB [testdb] CALL pro_testlog;
MariaDB [testdb] COMMIT; 在从节点上查看同步情况
MariaDB [(none)] SELECT COUNT(*) FROM testdb.testlog;
----------
| COUNT(*) |
----------
| 99999 | #同步成功
----------
MariaDB [(none)] SHOW SLAVE STATUS\G
*************************** 1. row ****************************Slave_IO_State: Waiting for master to send eventMaster_Host: 192.168.0.7Master_User: testuserMaster_Port: 3306Connect_Retry: 10Master_Log_File: master1-bin.000003Read_Master_Log_Pos: 10389814Relay_Log_File: relay-log.000002Relay_Log_Pos: 10389944Relay_Master_Log_File: master1-bin.000003Slave_IO_Running: Yes #IO线程已启动Slave_SQL_Running: Yes #SQL线程已启动Seconds_Behind_Master: 0 #主从复制的时间差Master_Server_Id: 1 2、旧数据库新加从服务器 1主服务器配置 ~]# vim /etc/my.cnf[mysqld]log_binbinlog_formatROWlog-basenamemaster1server_id1
~]# systemctl restart mariadb
~]# mysqldump -A -F --single-transaction --master-data1 full.sql
~]# scp full.sql root192.168.0.8:/root/
~]# mysql -e GRANT REPLICATION SLAVE ON *.* TO testuser192.168.0.8 IDENTIFIED BY testpass; 2从服务器配置 ~]# vim /etc/my.cnf[mysqld]server_id2relay_logrelay-log relay_log_indexrelay-log.indexread_onlyON
~]# systemctl restart mariadb
~]# vim full.sql #在备份的SQL文件中加入以下信息CHANGE MASTER TOMASTER_HOST192.168.0.7,MASTER_USERtestuser,MASTER_PASSWORDtestpass,MASTER_PORT3306,MASTER_LOG_FILEmaster1-bin.000005,MASTER_LOG_POS245,MASTER_CONNECT_RETRY10;
~]# mysql full.sql #导入SQL的同时配置已经完成
MariaDB [(none)] SELECT COUNT(*) FROM testdb.testlog;
----------
| COUNT(*) |
----------
| 99999 |
----------
MariaDB [(none)] START SLAVE; #启动复制 三、级联复制架构实现 1主节点 [rootmaster ~]# vim /etc/my.cnf
[mysqld]
log_bin
binlog_formatROW
log-basenamemaster
server_id1
[rootmaster ~]# systemctl restart mariadb
MariaDB [(none)] GRANT REPLICATION SLAVE ON *.* TO repluser192.168.0.% IDENTIFIED BY replpass;
MariaDB [(none)] SHOW MASTER LOGS;
------------------------------
| Log_name | File_size |
------------------------------
| master-bin.000001 | 26753 |
| master-bin.000002 | 921736 |
| master-bin.000003 | 401 |
------------------------------ 2从节点 [rootslave1 ~]# vim /etc/my.cnf
[mysqld]
log_bin #注意级联架构中中继从节点一定得开二进制日志功能
binlog_formatROW
read_onlyON
server_id2
log_slave_updates #这项为关键作用是将从服务的数据改变记录到二进制日志文件中
relay_logrelay-log
relay_log_indexrelay-log.index
[rootslave1 ~]# systemctl start mariadb
MariaDB [(none)] CHANGE MASTER TO- MASTER_HOST192.168.0.7,- MASTER_USERrepluser,- MASTER_PASSWORDreplpass,- MASTER_PORT3306,- MASTER_LOG_FILEmaster-bin.000003,- MASTER_LOG_POS401,- MASTER_CONNECT_RETRY10;
MariaDB [(none)] START SLAVE;
MariaDB [(none)] SHOW MASTER LOGS;
-------------------------------
| Log_name | File_size |
-------------------------------
| mariadb-bin.000001 | 245 |
-------------------------------
MariaDB [(none)] GRANT REPLICATION SLAVE ON *.* TO repluser192.168.0.% IDENTIFIED BY replpass; 3从节点的从节点 [rootslave2 ~]# vim /etc/my.cnf
[mysqld]
read_onlyON
server_id3
relay_logrelay-log
relay_log_indexrelay-log.index
[rootslave2 ~]# systemctl start mariadb
MariaDB [(none)] CHANGE MASTER TO- MASTER_HOST192.168.0.8,- MASTER_USERrepluser,- MASTER_PASSWORDreplpass,- MASTER_PORT3306,- MASTER_LOG_FILEmariadb-bin.000001,- MASTER_LOG_POS245,- MASTER_CONNECT_RETRY10;
MariaDB [(none)] START SLAVE; 4从节点的从节点2 [rootslave3 ~]# vim /etc/my.cnf
[mysqld]
read_onlyON
server_id4
relay_logrelay-log
relay_log_indexrelay-log.index
[rootslave3 ~]# systemctl start mariadb
MariaDB [(none)] CHANGE MASTER TO- MASTER_HOST192.168.0.8,- MASTER_USERrepluser,- MASTER_PASSWORDreplpass,- MASTER_PORT3306,- MASTER_LOG_FILEmariadb-bin.000001,- MASTER_LOG_POS245,- MASTER_CONNECT_RETRY10;
MariaDB [(none)] START SLAVE;
到此已经搭建好了级联复制接下来测试一下把~ 四、主主复制架构 容易产生的问题数据不一致因此慎用考虑要点自动增长id 配置一个节点使用奇数id auto_increment_offset1 开始点 auto_increment_increment2 增长幅度 另一个节点使用偶数id auto_increment_offset2 auto_increment_increment2 1主1 [mysqld]
log_bin
binlog_formatROW
log-basenamemaster1
server_id1
relay_logrelay-log
relay_log_indexrelay-log.index
auto_increment_offset1 #自增长字段从1开始
auto_increment_increment2 #每次增长2也就是说master1节点写入的数据的id字段全部是奇数
[rootmaster ~]# systemctl start mariadb
MariaDB [(none)] SHOW MASTER LOGS;
-------------------------------
| Log_name | File_size |
-------------------------------
| master1-bin.000001 | 27033 |
| master1-bin.000002 | 942126 |
| master1-bin.000003 | 245 |
-------------------------------
MariaDB [(none)] GRANT REPLICATION SLAVE ON *.* TO repluser192.168.0.% IDENTIFIED BY replpass;
MariaDB [(none)] CHANGE MASTER TO- MASTER_HOST192.168.0.8,- MASTER_USERrepluser,- MASTER_PASSWORDreplpass,- MASTER_PORT3306,- MASTER_LOG_FILEmaster2-bin.000003,- MASTER_LOG_POS245,- MASTER_CONNECT_RETRY10;
MariaDB [(none)] START SLAVE; 2主2 [mysqld]
log_bin
binlog_formatROW
log-basenamemaster2
server_id2
relay_logrelay-log
relay_log_indexrelay-log.index
auto_increment_offset2 #自增长字段从1开始
auto_increment_increment2 #每次增长2也就是说master1节点写入的数据的id字段全部是偶数
[rootmaster2 ~]# systemctl start mariadb
MariaDB [(none)] SHOW MASTER LOGS;
-------------------------------
| Log_name | File_size |
-------------------------------
| master2-bin.000001 | 27036 |
| master2-bin.000002 | 942126 |
| master2-bin.000003 | 245 |
-------------------------------
MariaDB [(none)] GRANT REPLICATION SLAVE ON *.* TO repluser192.168.0.% IDENTIFIED BY replpass;
MariaDB [(none)] CHANGE MASTER TO- MASTER_HOST192.168.0.7,- MASTER_USERrepluser,- MASTER_PASSWORDreplpass,- MASTER_PORT3306,- MASTER_LOG_FILEmaster1-bin.000003,- MASTER_LOG_POS245,- MASTER_CONNECT_RETRY10;
MariaDB [(none)] START SLAVE; 3测试 在master1上创建表增加数据
MariaDB [(none)] CREATE DATABASE db1;
MariaDB [(none)] use db1
MariaDB [db1] CREATE TABLE t1(id INT(2) AUTO_INCREMENT PRIMARY KEY,name CHAR(30));
MariaDB [db1] INSERT t1(name) VALUES (tom);
MariaDB [db1] INSERT t1(name) VALUES (maria);
MariaDB [db1] SELECT * FROM t1;
-----------
| id | name |
-----------
| 1 | tom |
| 3 | maria |
-----------
在master2上增加数据
MariaDB [db1] INSERT t1(name) VALUES (jerry);
MariaDB [db1] INSERT t1(name) VALUES (tony);
MariaDB [db1] SELECT * FROM t1;
-----------
| id | name |
-----------
| 1 | tom |
| 3 | maria |
| 4 | jerry |
| 6 | tony | 五、半同步复制的实现 默认情况下MySQL的复制功能是异步的异步复制可以提供最佳的性能主库把binlog日志发送给从库即结束并不验证从库是否接收完毕。这意味着当主服务器或从服务器端发生故障时有可能从服务器没有接收到主服务器发送过来的binlog日志这就会造成主服务器和从服务器的数据不一致甚至在恢复时造成数据的丢失半同步复制的机制是只有当主节点和从节点同步完成仅有一台同步完成即可返回写入完成这样的机制保证了数据的安全性。 1主节点 [rootmaster ~]# vim /etc/my.cnf
[mysqld]
log_bin
binlog_formatROW
log-basenamemaster
server_id1
relay_logrelay-log
relay_log_indexrelay-log.index
[rootmaster ~]# systemctl restart mariadb
MariaDB [(none)] GRANT REPLICATION SLAVE ON *.* TO repluser192.168.0.% IDENTIFIED BY replpass;
MariaDB [(none)] SHOW MASTER LOGS;
------------------------------
| Log_name | File_size |
------------------------------
| master-bin.000001 | 26753 |
| master-bin.000002 | 921736 |
| master-bin.000003 | 401 |
------------------------------
MariaDB [(none)] INSTALL PLUGIN rpl_semi_sync_master SONAME semisync_master.so; #安装模块
MariaDB [(none)] SET GLOBAL rpl_semi_sync_master_enabled1; #开启半同步功能
MariaDB [(none)] SHOW GLOBAL VARIABLES LIKE %semi%;
-------------------------------------------
| Variable_name | Value |
-------------------------------------------
| rpl_semi_sync_master_enabled | ON | #已开启
| rpl_semi_sync_master_timeout | 10000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_no_slave | ON |
-------------------------------------------
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 | 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 |
--------------------------------------------------- 2从节点1 [rootslave1 ~]# vim /etc/my.cnf
[mysqld]
read_onlyON
log_bin
binlog_formatROW
log-basenameslave
server_id2
relay_logrelay-log
relay_log_indexrelay-log.index
[rootslave1 ~]# systemctl restart mariadb
MariaDB [(none)] CHANGE MASTER TO- MASTER_HOST192.168.0.7,- MASTER_USERrepluser,- MASTER_PASSWORDreplpass,- MASTER_PORT3306,- MASTER_LOG_FILEmaster-bin.000003,- MASTER_LOG_POS401,- MASTER_CONNECT_RETRY10;
MariaDB [(none)] INSTALL PLUGIN rpl_semi_sync_slave SONAME semisync_slave.so;
MariaDB [(none)] SET GLOBAL rpl_semi_sync_slave_enabled1;
MariaDB [(none)] START SLAVE;
MariaDB [(none)] SHOW MASTER LOGS;
-----------------------------
| Log_name | File_size |
-----------------------------
| slave-bin.000001 | 26753 |
| slave-bin.000002 | 921736 |
| slave-bin.000003 | 245 |
-----------------------------
MariaDB [(none)] GRANT REPLICATION SLAVE ON *.* TO repluser192.168.0.% IDENTIFIED BY replpass; 3从节点2 [rootslave2 ~]# vim /etc/my.cnf
[mysqld]
read_onlyON
server_id3
relay_logrelay-log
relay_log_indexrelay-log.index
[rootslave2 ~]# systemctl restart mariadb
MariaDB [(none)] CHANGE MASTER TO- MASTER_HOST192.168.0.8,- MASTER_USERrepluser,- MASTER_PASSWORDreplpass,- MASTER_PORT3306,- MASTER_LOG_FILEslave-bin.000003,- MASTER_LOG_POS245,- MASTER_CONNECT_RETRY10;
MariaDB [(none)] START SLAVE; 4从节点3 [rootslave3 ~]# vim /etc/my.cnf
[mysqld]
read_onlyON
server_id4
relay_logrelay-log
relay_log_indexrelay-log.index
[rootslave3 ~]# systemctl restart mariadb
MariaDB [(none)] CHANGE MASTER TO- MASTER_HOST192.168.0.8,- MASTER_USERrepluser,- MASTER_PASSWORDreplpass,- MASTER_PORT3306,- MASTER_LOG_FILEslave-bin.000003,- MASTER_LOG_POS245,- MASTER_CONNECT_RETRY10;
MariaDB [(none)] START SLAVE; 六、加密传输复制的实现 在默认的主从复制过程或远程连接到MySQL/MariaDB所有的链接通信中的数据都是明文的外网里访问数据或则复制存在安全隐患。通过SSL/TLS加密的方式进行复制的方法来进一步提高数据的安全性 主服务器开启SSL[mysqld] 加一行ssl 主服务器配置证书和私钥并且创建一个要求必须使用SSL连接的复制账号 从服务器使用CHANGER MASTER TO 命令时指明ssl相关选项 MariaDB [(none)] SHOW VARIABLES LIKE %ssl%;
-------------------------
| Variable_name | Value |
-------------------------
| have_openssl | DISABLED |
| have_ssl | DISABLED |
| ssl_ca | |
| ssl_capath | |
| ssl_cert | |
| ssl_cipher | |
| ssl_key | |
------------------------- 特别提示在配置之前先检查mysql服务是否支持ssl功能如果have_ssl的值为DISABLED则支持如果为NO则不支持需要再重新编译安装或者安装具有ssl功能的版本 1CA [rootCA ~]# mkdir /etc/my.cnf.d/ssl/
[rootCA ~]# cd /etc/my.cnf.d/ssl/
[rootCA ssl]# openssl genrsa 2048 cakey.pem
[rootCA ssl]# openssl req -new -x509 -key cakey.pem -out cacert.pem -days 3650 #自签证书Country Name (2 letter code) [XX]:CNState or Province Name (full name) []:beijingLocality Name (eg, city) [Default City]:beijingOrganization Name (eg, company) [Default Company Ltd]:testmysqlca Organizational Unit Name (eg, section) []:optCommon Name (eg, your name or your servers hostname) []:ca.testmysqlca.com[rootCA ssl]# openssl req -newkey rsa:2048 -days 365 -nodes -keyout master.key master.csrCountry Name (2 letter code) [XX]:CNState or Province Name (full name) []:beijingLocality Name (eg, city) [Default City]:beijingOrganization Name (eg, company) [Default Company Ltd]:testmysqlcaOrganizational Unit Name (eg, section) []:optCommon Name (eg, your name or your servers hostname) []:master.testmysqlca.com
[rootCA ssl]# openssl x509 -req -in master.csr -CA cacert.pem -CAkey cakey.pem -set_serial 01 master.crt #签署master证书[rootCA ssl]# openssl req -newkey rsa:2048 -days 365 -nodes -keyout slave.key slave.csrCountry Name (2 letter code) [XX]:CNState or Province Name (full name) []:beijingLocality Name (eg, city) [Default City]:beijingOrganization Name (eg, company) [Default Company Ltd]:testmysqlca Organizational Unit Name (eg, section) []:optCommon Name (eg, your name or your servers hostname) []:slave.testmysqlca.com
[rootCA ssl]# openssl x509 -req -in slave.csr -CA cacert.pem -CAkey cakey.pem -set_serial 02 slave.crt #签署slave证书[rootCA ssl]# openssl req -newkey rsa:2048 -days 365 -nodes -keyout slave2.key slave2.csr
Country Name (2 letter code) [XX]:CN
State or Province Name (full name) []:beijing
Locality Name (eg, city) [Default City]:beijing
Organization Name (eg, company) [Default Company Ltd]:testmysqlca
Organizational Unit Name (eg, section) []:opt
Common Name (eg, your name or your servers hostname) []:slave2.testmysqlca.com
[rootCA ssl]# openssl x509 -req -in slave2.csr -CA cacert.pem -CAkey cakey.pem -set_serial 03 slave2.crt #签署slave2证书[rootCA ssl]# openssl verify -CAfile cacert.pem master.crt slave.crt slave2.crt #检查证书是否可用
master.crt: OK
slave.crt: OK
slave2.crt: OK
先在各个节点上创建/etc/my.cnf.d/ssl/文件夹将各自的证书CA的证书和各自的秘钥文件复制过去
[rootCA ssl]# scp cacert.pem master.crt master.key root192.168.0.7:/etc/my.cnf.d/ssl/
[rootCA ssl]# scp cacert.pem slave.crt slave.key root192.168.0.8:/etc/my.cnf.d/ssl/
[rootCA ssl]# scp cacert.pem slave2.crt slave2.key root192.168.0.9:/etc/my.cnf.d/ssl/ 2master [rootmaster ~]# mkdir /etc/my.cnf.d/ssl/
[rootmaster ~]# vim /etc/my.cnf
[mysqld]
log_bin
binlog_formatROW
log-basenamemaster
server_id1
ssl #开启ssl功能
ssl-ca/etc/my.cnf.d/ssl/cacert.pem #指定CA证书的路径
ssl-cert/etc/my.cnf.d/ssl/master.crt #指定自己的证书的路径
ssl-key/etc/my.cnf.d/ssl/master.key #指定自己的秘钥文件路径
[rootmaster ~]# systemctl restart mariadb
MariaDB [(none)] GRANT REPLICATION SLAVE ON *.* TO repluser192.168.0.% IDENTIFIED BY replpass REQUIRE SSL; #授权用户并且强制迫使用户开启ssl登录
MariaDB [(none)] SHOW MASTER LOGS;
------------------------------
| Log_name | File_size |
------------------------------
| master-bin.000001 | 26753 |
| master-bin.000002 | 921736 |
| master-bin.000003 | 413 |
------------------------------ 3slave1 [rootslave1 ~]# mkdir /etc/my.cnf.d/ssl/
[rootslave1 ~]# mysql -urepluser -preplpass -h192.168.0.7 --ssl-ca/etc/my.cnf.d/ssl/cacert.pem --ssl-cert/etc/my.cnf.d/ssl/slave.crt --ssl-key/etc/my.cnf.d/ssl/slave.key
[rootslave1 ~]# vim /etc/my.cnf
[mysqld]
read_onlyON
server_id2
relay_logrelay-log
relay_log_indexrelay-log.index
ssl
ssl-ca/etc/my.cnf.d/ssl/cacert.pem
ssl-cert/etc/my.cnf.d/ssl/slave.crt
ssl-key/etc/my.cnf.d/ssl/slave.key
[rootslave1 ~]# systemctl restart mariadb
MariaDB [(none)] CHANGE MASTER TO- MASTER_HOST192.168.0.7,- MASTER_USERrepluser,- MASTER_PASSWORDreplpass,- MASTER_PORT3306,- MASTER_LOG_FILEmaster-bin.000003,- MASTER_LOG_POS413,- MASTER_CONNECT_RETRY10,- MASTER_SSL1; #注意需要指明开启ssl链接
MariaDB [(none)] START SLAVE; 4slave2 [rootslave2 ~]# mkdir /etc/my.cnf.d/ssl/
[rootslave2 ~]# mysql -urepluser -preplpass -h192.168.0.7 --ssl-ca/etc/my.cnf.d/ssl/cacert.pem --ssl-cert/etc/my.cnf.d/ssl/slave2.crt --ssl-key/etc/my.cnf.d/ssl/slave2.key
[rootslave2 ~]# vim /etc/my.cnf
[mysqld]
read_onlyON
server_id3
relay_logrelay-log
relay_log_indexrelay-log.index
ssl
ssl-ca/etc/my.cnf.d/ssl/cacert.pem
ssl-cert/etc/my.cnf.d/ssl/slave2.crt
ssl-key/etc/my.cnf.d/ssl/slave2.key
[rootslave2 ~]# systemctl restart mariadb
MariaDB [(none)] CHANGE MASTER TO- MASTER_HOST192.168.0.7,- MASTER_USERrepluser,- MASTER_PASSWORDreplpass,- MASTER_PORT3306,- MASTER_LOG_FILEmaster-bin.000003,- MASTER_LOG_POS413,- MASTER_CONNECT_RETRY10,- MASTER_SSL1;
MariaDB [(none)] START SLAVE; 七、MySQL复制的相关指令和变量总结 选项 log_bin 启用二进制日志在主节点或级联复制中间的从节点必须要开启binlog_formatROW 二进制日志记录方式为基于行的方式记录强烈建议开启log-basenamemaster | slave ... 二进制日志的前缀名不是必须向但建议标识server_id # 服务器ID各个节点的ID必须唯一relay_log relay-log 开启中继日志并以relay-log为文件名开头从节点开启relay_log_index relay-log.index 中继日志索引文件log_slave_updates 作用是SQL线程重读中继日志时将改变数据的操作记录为二进制日志在级联复制中使用ssl 开启ssl功能 ssl-ca/etc/my.cnf.d/ssl/cacert.pemssl-cert/etc/my.cnf.d/ssl/master.crtssl-key/etc/my.cnf.d/ssl/master.keysync_binlog1 每次写后立即同步二进制日志到磁盘 innodb_flush_log_at_trx_commit1 每次事务提交立即同步日志写磁盘sync_master_info# #次事件后master.info同步到磁盘skip_slave_startON 不自动启动slavesync_relay_log# #次写后同步relay log到磁盘sync_relay_log_info# #次事务后同步relay-log.info到磁盘auto_increment_offset1 自动增长开始点在主主复制中使用auto_increment_increment2 增长幅度变量 replicate_do_db 指定复制库的白名单replicate_ignore_db 指定复制库黑名单replicate_do_table 指定复制表的白名单replicate_ignore_table 指定复制表的黑名单replicate_wild_do_table foo%.bar% 支持通配符replicate_wild_ignore_table 指定复制的表黑名单rpl_semi_sync_slave_enabled1 开启半同步复制需要安装模块指令 START SLAVE; 启动主从复制STOP SLAVE; 停止复制SHOW SLAVE STATUS; 查看复制状态 Seconds_Behind_Master: 0 从服务器是否落后于主服务RESET SLAVE ALL; 重置从服务器的配置MASTER_SSL1, 配合 CHANGE MASTER TO 使用开启ssl加密复制 MASTER_SSL_CA /etc/my.cnf.d/ssl/cacert.pem,MASTER_SSL_CERT /etc/my.cnf.d/ssl/slave.crt,MASTER_SSL_KEY /etc/my.cnf.d/ssl/slave.key;PURGE { BINARY | MASTER } LOGS { TO log_name | BEFORE datetime_expr } 删除二进制日志谨慎操作SHOW MASTER STATUS 查看二进制日志状态SHOW BINLOG EVENTS 查看二进制日志SHOW BINARY LOGS 查看二进制日志SHOW PROCESSLIST 查看进程感谢阅读 个人学习记录 2018.6.16 15:01 转载于:https://www.cnblogs.com/L-dongf/p/9190565.html
