一、主从复制的原理
二、设置MySQL主从配置的优点
三、主从数据库服务器的配置
四、测试主从复制各种数据改变状态
五、监控服务器的状态及常用相关信息
六、主从切换
================================================================
一、主从复制的原理
分为同步复制和异步复制，实际复制架构中大部分为异步复制
复制常用架构
Mysql复制环境90%以上都是一个Master带一个或者多个Slave的架构模式，主要用于读压力比较大的应用的数据库端廉价扩展解决方案。因为只要master和slave的压力不是太大（尤其是slave端压力）的话，异步复制的延时一般都很少很少。尤其是自slave端的复制方式改成两个进程处理之后，更是减小了slave端的延时。而带来的效益是，对于数据实时性要求不是特别的敏感度的应用，只需要通过廉价的pc?server来扩展slave的数量，将读压力分散到多台slave的机器上面，即可解决数据库端的读压力瓶颈。这在很大程度上解决了目前很多中小型网站的数据库压力瓶颈问题，甚至有些大型网站也在使用类似方案解决数据库瓶颈
MySQL支持的复制类型：
（１）：基于语句的复制： 在主服务器上执行的SQL语句，在从服务器上执行同样的语句。MySQL默认采用基于语句的复制， 效率比较高。
一旦发现没法精确复制时， 会自动选着基于行的复制。
（２）：基于行的复制：把改变的内容复制过去，而不是把命令在从服务器上执行一遍. 从mysql5.0开始支持
（３）：混合类型的复制: 默认采用基于语句的复制，一旦发现基于语句的无法精确的复制时，就会采用基于行的复制。
MySQL复制技术有以下一些特点：
(1) 数据分布 (Datadistribution )
(2) 负载平衡(load balancing)
(3) 备份(Backups)
(4) 高可用性和容错行 Highavailability and failove
1. Slave 上面的IO线程连接上 Master，并请求从指定日志文件的指定位置(或者从最开始的日志)之后的日志内容;
2. Master 接收到来自 Slave 的 IO 线程的请求后，通过负责复制的 IO线程根据请求信息读取指定日志指定位置之后的日志信息，返回给 Slave 端的 IO线程。返回信息中除了日志所包含的信息之外，还包括本次返回的信息在 Master 端的 Binary Log 文件的名称以及在 BinaryLog 中的位置;
3. Slave 的 IO 线程接收到信息后，将接收到的日志内容依次写入到 Slave 端的RelayLog文件(MySQL-relay-bin.xxxxxx)的最末端，并将读取到的Master端的bin-log的文件名和位置记录到master-info文件中，以便在下一次读取的时候能够清楚的高速Master“我需要从某个bin-log的哪个位置开始往后的日志内容，请发给我”
4. Slave 的 SQL 线程检测到 RelayLog 中新增加了内容后，会马上解析该 Log 文件中的内容成为在 Master端真实执行时候的那些可执行的 Query 语句，并在自身执行这些 Query。这样，实际上就是在 Master 端和 Slave端执行了同样的 Query，所以两端的数据是完全一样的。
==================================================================
三、主从数据库服务器的配置
MySQL 主从配置
主库 192.168.14.76
从库 192.168.14.111
#配置前准备
#检查两台服务器的两种iptables,selinux
#关闭iptables的方法
1.检查iptables是否处于工作状态
root@mdb01 ~]# service iptables status
Table: filter
Chain INPUT (policy ACCEPT)
num target prot opt source destination
1 ACCEPT all -- 0.0.0.0/0 0.0.0.0/0 state RELATED,ESTABLISHED
2 ACCEPT icmp -- 0.0.0.0/0 0.0.0.0/0
3 ACCEPT all -- 0.0.0.0/0 0.0.0.0/0
4 ACCEPT tcp -- 0.0.0.0/0 0.0.0.0/0 state NEW tcp dpt:22
5 REJECT all -- 0.0.0.0/0 0.0.0.0/0 reject-with icmp-host-prohibited
Chain FORWARD (policy ACCEPT)
num target prot opt source destination
1 REJECT all -- 0.0.0.0/0 0.0.0.0/0 reject-with icmp-host-prohibited
Chain OUTPUT (policy ACCEPT)
num target prot opt source destination
#如果iptables没有运行就是下面这个样子
[root@mdb01 ~]# service iptables status
iptables: Firewall is not running.
2.关闭iptables
[root@mdb01 ~]# service iptables stop
iptables: Setting chains to policy ACCEPT:filter [ OK ]
iptables: Flushing firewall rules: [ OK ]
iptables: Unloading modules: [ OK ]
3.禁用iptables
[root@mdb01 ~]# chkconfig iptables off
#禁用selinux
[root@mdb01 ~]# vi /etc/selinux/config
# This file controls the state of SELinuxon the system.
# SELINUX= can take one of these threevalues:
# enforcing - SELinux security policy is enforced.
# permissive - SELinux prints warnings instead of enforcing.
# disabled - No SELinux policy is loaded.
#SELINUX=enforcing #禁用selinux选项
SELINUX=disable
# SELINUXTYPE= can take one of these twovalues:
# targeted - Targeted processes are protected,
# mls - Multi Level Security protection.
SELINUXTYPE=targeted
#保存退出后 ,重启linux
#注意本机帐户是否有远程访问权限
==========================================================================
#查看mysql是否处在运行状态
[root@mdb01 ~]# service mysqld status
SUCCESS! MySQL running (1118)
[root@mdb01 ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.39-log MySQL CommunityServer (GPL)
Copyright (c) 2000, 2014, Oracle and/or itsaffiliates. All rights reserved.
Oracle is a registered trademark of OracleCorporation and/or its
affiliates. Other names may be trademarksof their respective
owners.
Type 'help;' or '\h' for help. Type '\c' toclear the current input statement.
mysql>
mysql> use mysql;
Database changed
#管理员用户加密码和删除匿名用户及其它无用用户
mysql> drop user ''@localhost;
Query OK, 0 rows affected (0.01 sec)
mysql> use mysql;
Database changed
mysql> select user,host,password fromuser;
+------+-----------------+----------+
| user | host | password |
+------+-----------------+----------+
| root | localhost | |
| root | lenovo-581a59b5 | |
| root | 127.0.0.1 | |
| root | ::1 | |
| | localhost | |
| | lenovo-581a59b5 | |
+------+-----------------+----------+
6 rows in set (0.00 sec)
mysql> drop user ''@'lenovo-581a59b5';
Query OK, 0 rows affected (0.00 sec)
mysql> drop user 'root'@'::1'; #删除ipv6监听用户
Query OK, 0 rows affected (0.00 sec)
mysql> update user setpassword=password('oracle') where user='root';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
mysql> delete from user where user='';
Query OK, 1 row affected (0.00 sec)
mysql> select user,host,password fromuser;
+------+-----------------+-------------------------------------------+
| user | host | password |
+------+-----------------+-------------------------------------------+
| root | localhost |*2447D497B9A6A15F2776055CB2D1E9F86758182F |
| root | lenovo-581a59b5 |*2447D497B9A6A15F2776055CB2D1E9F86758182F |
| root | 127.0.0.1 |*2447D497B9A6A15F2776055CB2D1E9F86758182F |
+------+-----------------+-------------------------------------------+
3 rows in set (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> drop database test;
Query OK, 0 rows affected (0.00 sec)
mysql>\q
Bye
==========================================================================
3、配置文件。
在两个机器上的my.cnf里面都开启二进制日志。
主库
vi /etc/my.cnf
[mysqld]
user = mysql
log_bin=mysql-bin
server_id = 763306
binlog_format = mixed
#binlog_do_db=test1
#binlog_ignore_db=mysql
#binlog_ignore_db=information_schema
#binlog_ignore_db=performance_schema
log_error =/usr/local/mysql/log/mysql-error.log
socket = /usr/local/mysql/mysql.sock
log-slave-updates
#配置文件中不要有其它错误信息，要详细检查
#[root@mdb01 ~]# mkdir -p/usr/local/mysql/log/ 以便存放error log
修改完成请重启MYSQL服务器
[root@mdb01 ~]# service mysqld restart
Shutting down MySQL. SUCCESS!
Starting MySQL.. SUCCESS!
[root@mdb01 ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.39-log MySQL CommunityServer (GPL)
Copyright (c) 2000, 2014, Oracle and/or itsaffiliates. All rights reserved.
Oracle is a registered trademark of OracleCorporation and/or its
affiliates. Other names may be trademarksof their respective
owners.
Type 'help;' or '\h' for help. Type '\c' toclear the current input statement.
mysql>
(授权用户)
mysql> grant replication slave on *.* to'master'@'%'identified by 'oracle';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
-------------------------------------------------
从库
vi /etc/my.cnf
user = mysql
port = 3306
read_only = on
binlog_format = mixed
log_bin = mysql-bin
server_id = 1113306
#replicate_do_db = test1
#replicate_ignore_db = test
#replicate_ignore_db = mysql
#replicate_ignore_db = information_schema
#replicate_ignore_db = performance_schema
replicate_wild_do_table=test1.%
replicate_wild_do_table=testa.%
replicate_wild_ignore_table=information_schema.%
replicate_wild_ignore_table=performance_schema.%
slave-skip-error = all
read-only = 1
log_error =/usr/local/mysql/log/mysql-error.log
socket = /usr/local/mysql/mysql.sock
#配置文件中不要有其它错误信息，要详细检查
==================================================
2、授权用户。
主库
本机登录MYSQL的方法
[root@test-mail ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.45-log Sourcedistribution
Type 'help;' or '\h' for help. Type '\c' toclear the buffer.
mysql>
(授权用户)
mysql> grant replication slave on *.* to'master'@'%'identified by 'oracle';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
#配置文件中不要有其它错误信息，要详细检查
-------------------------------------------------------------------------------------------
从库
登录MYSQL
[root@test-mail2 ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.45-log Sourcedistribution
Type 'help;' or '\h' for help. Type '\c' toclear the buffer.
mysql>
授权用户
mysql> grant replication slave on *.* to'master'@'%'identified by 'oracle';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
#[root@mdb02 ~]# mkdir -p/usr/local/mysql/log/ 以便存放error log
修改完成请重启MYSQL服务器
==========================================================================================
主库:
测试授权帐户是否OK，请192.168.14.76,192.168.14.111互访登录就OK
root@test-mail ~]# mysql -h 192.168.14.111-u master -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.0.45-log Sourcedistribution
Type 'help;' or '\h' for help. Type '\c' toclear the buffer.
mysql>
mysql> create database test1;
Query OK, 1 row affected (0.00 sec)
mysql> use test1;
Database changed
mysql> CREATE TABLE t1(id int not nullprimary key,name char(20));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t1 values("1","123");
Query OK, 1 row affected (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t1;
+----+------+
| id | name |
+----+------+
| 1| 123 |
+----+------+
1 row in set (0.02 sec)
============================================================================
主库
mysql>show master status;
+------------------+----------+--------------+------------------------------------------------+
| File | Position | Binlog_Do_DB |Binlog_Ignore_DB |
+------------------+----------+--------------+------------------------------------------------+
| mysql-bin.000006 | 325 | test1 |mysql,information_schema,db=performance_schema |
+------------------+----------+--------------+------------------------------------------------+
1 row in set (0.00 sec)
从库
mysql> CHANGE MASTER TOMASTER_HOST='192.168.14.76',
-> MASTER_USER='master',
-> MASTER_PASSWORD='oracle',
-> MASTER_LOG_FILE='mysql-bin.000020',
-> MASTER_LOG_POS=107;
----------------------------------------------------------------------------------------------------------------------------------------1
#在生产库上数据迁移可以使用拷贝文件实现
FLUSH TABLES WITH READ LOCK;
使用tar打包，复制到从服务器上
SHOW MASTER STATUS;–记录下当前快照是的日志文件情况
UNLOCK TABLES;
----------------------------------------------------------------------------------------------------------------------------------------1
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
==============================================================================
从库
#MySQL从库复制无效Slave_IO_Running:No，注意/etc/my.cnf中server id 是否与其它库相同？
mysql> show slave status\G
*************************** 1. row***************************
Slave_IO_State: Waiting formaster to send event
Master_Host: 192.168.14.76
Master_User: master
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000006
Read_Master_Log_Pos: 325
Relay_Log_File:mdb02-relay-bin.000002
Relay_Log_Pos: 253
Relay_Master_Log_File: mysql-bin.000006
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: test1
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: 325
Relay_Log_Space: 409
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: 763306
1 row in set (0.02 sec)
Slave_IO_Running:Yes
Slave_SQL_Running:Yes
#以上这两个参数的值为Yes，即说明配置成功！
===============================================================
四、测试主从复制各种数据改变状态
建表测试AB点是否真正OK。
主库
mysql> create database test2;
Query OK, 1 row affected (0.00 sec)
mysql> CREATE TABLE t1(id int not nullprimary key,name char(20));
Query OK, 0 rows affected (0.06 sec)
mysql> insert into t1 values("1","123");
Query OK, 1 row affected (0.02 sec)
去B点查看，表是否已经同步过去
mysql> use test1; #这时候从库也被创建完成
Database changed
mysql> select * from t1; #这时候从表也被创建完成
+----+------+
| id | name |
+----+------+
| 1| 123 |
+----+------+
1 row in set (0.00 sec)
从库
对同表插入数据查看A点表是否同步数据
mysql> insert into t1values("2","456");
Query OK, 1 row affected (0.03 sec)
mysql> select * from t1;
+----+------+
| id | name |
+----+------+
| 1| 123 |
| 2| 456 |
+----+------+
2 rows in set (0.00 sec)
现在看来，两边数据已互通master to master 两边同步方式架设成功。
==============================================================
五、监控服务器的状态及常用相关信息
测试1.
主库创建表是从库是否会同步
主库
mysql> create table testb (id intunsigned auto_increment not null,name char(20),primary key (id));Query OK, 0rows affected (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
--------------------------------------------------------------
从库
mysql> select * from testb;
Empty set (0.00 sec)
#创建表会同步
==============================================================
测试2
数据插入A点B是否会同步
主库
mysql> use test1;
Database changed
mysql> select * from testa;
+----+------+
| id | name |
+----+------+
| 1| 1 |
| 2| 2 |
| 3| 3 |
| 4| 5 |
+----+------+
4 rows in set (0.00 sec)
mysql> insert into testavalues("name","6");
Query OK, 1 row affected, 1 warning (0.04sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
-------------------------------------------------
从库
mysql> select * from testa;
+----+------+
| id | name |
+----+------+
| 1| 1 |
| 2| 2 |
| 3| 3 |
| 4| 5 |
| 5| 6 |
+----+------+
5 rows in set (0.00 sec)
OK，两边同步update可以
===================================================
测试3
主库数据更新从库是否会同步更新
主库
mysql> select * from testa;
+----+------+
| id | name |
+----+------+
| 1| 1 |
| 2| 2 |
| 3| 3 |
| 4| 5 |
| 5| 6 |
+----+------+
5 rows in set (0.00 sec)
mysql> update user set name='4' where id= '4';
ERROR 1146 (42S02): Table 'test1.user'doesn't exist
mysql> update testa set name='4' whereid = '4';
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
#两边表数据update也可以同步
---------------------------------------------------------
从库
mysql> select * from testa;
+----+------+
| id | name |
+----+------+
| 1| 1 |
| 2| 2 |
| 3| 3 |
| 4| 4 |
| 5| 6 |
+----+------+
5 rows in set (0.00 sec)
=========================================================
测试4
测试主库点数据删除B库是否会同步删除动作
主库
mysql> select * from testa;
+----+------+
| id | name |
+----+------+
| 1| 1 |
| 2| 2 |
| 3| 3 |
| 4| 4 |
| 5| 6 |
+----+------+
5 rows in set (0.00 sec)
mysql> delete from testa where id = '5';
Query OK, 1 row affected (0.04 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from testa;
+----+------+
| id | name |
+----+------+
| 1| 1 |
| 2| 2 |
| 3| 3 |
| 4| 4 |
+----+------+
4 rows in set (0.00 sec)
------------------------------------------------------------
从库
mysql> select * from testa;
+----+------+
| id | name |
+----+------+
| 1| 1 |
| 2| 2 |
| 3| 3 |
| 4| 4 |
+----+------+
4 rows in set (0.00 sec)
==============================================================
测试5
检查双边数据库创建是否可以同步
从库停止复制
mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)
主库
[root@master ~]# vi /etc/my.cnf
[mysqld]
user = mysql
log_bin=mysql-bin
server-id = 763306
binlog_format = mixed
#binlog_do_db= test1
#binlog_do_db= test2
#binlog_ignore_db= mysql
#binlog_ignore_db= information_schema
#binlog_ignore_db= performance_schema
log-slave-updates
log_error =/usr/local/mysql/log/mysql-error.log
#socket = /tmp/mysql.sock
#主库不用更改信息
-------------------------------------------
从库
[root@slave ~]# vi /etc/my.cnf
user = mysql
port = 3306
read_only = on
binlog_format = mixed
log_bin = mysql-bin
server_id = 1113306
#replicate_do_db = test1
#replicate_do_db = test2
#replicate_ignore_db = mysql
#replicate_ignore_db = information_schema
#replicate_ignore_db = performance_schema
#replicate_ignore_db = test
replicate_wild_do_table = test1.%
replicate_wild_do_table = testa.% #添加要复制的数据库
replicate_wild_ignore_table = mysql.%
replicate_wild_ignore_table =information_schema.%
replicate_wild_ignore_table =performance_schema.%
log_error =/usr/local/mysql/log/mysql-error.log
slave-skip-errors=all
#保存退出
从库
[root@slave ~]# service mysqld restart
Shutting down MySQL.... SUCCESS!
Starting MySQL.. SUCCESS! #重启从库使其配置生效
mysql> stop slave; #停用从库复制
Query OK, 0 rows affected, 1 warning (0.00sec)
#观看主库binlog,position
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB |Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000009 | 107 | | |
+------------------+----------+--------------+------------------+
从库执行如下命令复制
mysql> CHANGE MASTER TOMASTER_HOST='192.168.14.76',
-> MASTER_USER='master',
-> MASTER_PASSWORD='oracle',
-> MASTER_LOG_FILE='mysql-bin.000009',
-> MASTER_LOG_POS=107;
Query OK, 0 rows affected (0.05 sec)
mysql> start slave; #启动从库
Query OK, 0 rows affected (0.00 sec)
#检查从库参数状态是否正确
mysql> show slave status\G
*************************** 1. row***************************
Slave_IO_State: Waiting formaster to send event
Master_Host: 192.168.14.76
Master_User: master
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000009
Read_Master_Log_Pos: 670
Relay_Log_File:slave-relay-bin.000002
Relay_Log_Pos: 816
Relay_Master_Log_File: mysql-bin.000009
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table: test1.%,testa.%
Replicate_Wild_Ignore_Table:mysql.%,information_schema.%,performance_schema.%
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 670
Relay_Log_Space: 972
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: 763306
1 row in set (0.00 sec)
------------------------------------------------------
主库创建数据库test2
mysql> create database testa;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test1 |
| testa |
+--------------------+
5 rows in set (0.00 sec)
mysql> use testa;
Database changed
mysql> create table t1 (id int unsignedauto_increment not null,name char(20),primary key (id));
Query OK, 0 rows affected (0.07 sec)
mysql> use testa;
Database changed
mysql> select * from t1;
Empty set (0.00 sec)
mysql> desc t1;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | char(20) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> insert into t1 values("name","aaa");
Query OK, 1 row affected, 1 warning (0.04sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t1;
+----+------+
| id | name |
+----+------+
| 0| aaa |
+----+------+
1 row in set (0.00 sec)
------------------------------------------------------------
从库查看复制结果
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test1 |
| testa |
+--------------------+
5 rows in set (0.00 sec)
mysql>
mysql> use testa;
Database changed
mysql> select * from t1;
+----+------+
| id | name |
+----+------+
| 0| aaa |
+----+------+
1 row in set (0.00 sec)
OK，主从库create database也可以.
==============================================================
MySQL常用命令
#查看数据引擎
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints|
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAMtables | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything youwrite to it disappears) | NO |NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful fortemporary tables | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions,row-level locking, and foreign keys |YES | YES | YES |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine |NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)
#加全局锁表以免在复制过程中数据发生变化
mysql>flush tables with read lock;
#解锁
mysql>unlock tables;
#禁止产生binlog(防止操作被同步)
mysql>set session sql_log_bin = 0;
#导入完成后继续开启binlog实现同步
mysql>set session sql_log_bin = 1
执行 SHOW VARIABLES LIKE "%log_bin%"; 查看主服务器的binlog是否开启。 log_bin这项 为 ON 的话就表示已开启.
mysql> SHOW VARIABLES LIKE "%log_bin%";
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| log_bin | ON |
| log_bin_trust_function_creators |OFF |
| sql_log_bin | ON |
+---------------------------------+-------+
3 rows in set (0.00 sec)
mysql> show variables like '%semi%';
Empty set (0.01 sec)
=======================================================================
如何监控MySQL主从复制状态
可以通过：show slave status\G来查看，另外如果从数据库在复制的过程中出现问题，
可以通过命令reset slave从数据库服务器复制的线程，从数据库服务器的通常操作命令有：
start slave； ####启动复制线程
stop slave； ####停止复制线程
reset slave； ####重置复制线程
change master to；###动态改变到主服务器的配置
show slave status --显示复制线程的状态
show slave status\G --显示复制线程的状态（分行显示）
show master status\G --显示主数据库的状态（分行显示）
show master logs --显示主数据库日志，需在主数据库上运行
show processlist --显示有哪些线程在运行
#在看看binlog里的事务内容，这里一行代表一个事务
mysql> SHOW BINLOG EVENTS in'mysql-bin.000009';
+------------------+-----+-------------+-----------+-------------+-------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+-------------+-----------+-------------+-------------------------------------------------------------------------+
| mysql-bin.000009 | 4 | Format_desc | 763306 | 107 | Server ver: 5.5.39-log, Binlogver: 4 |
| mysql-bin.000009 | 107 | Query | 763306 | 192 | createdatabase testa |
| mysql-bin.000009 | 192 | Query | 763306 | 314 | use`testa`; CREATE TABLE t1(id int not null primary key,name char(20)) |
| mysql-bin.000009 | 314 | Query | 763306 | 394 | use`testa`; flush privileges |
| mysql-bin.000009 | 394 | Query | 763306 | 463 | BEGIN |
| mysql-bin.000009 | 463 | Query | 763306 | 563 | use`testa`; insert into t1 values ("name","aaa") |
| mysql-bin.000009 | 563 | Xid | 763306 | 590 | COMMIT /*xid=29 */ |
| mysql-bin.000009 | 590 | Query | 763306 | 670 | use`testa`; flush privileges |
+------------------+-----+-------------+-----------+-------------+-------------------------------------------------------------------------+
[root@master ~]# ps -aux|grep mysql
Warning: bad syntax, perhaps a bogus '-'?See /usr/share/doc/procps-3.2.8/FAQ
root 821 0.0 0.1 108300 1648 ? S 10:12 0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/mydata/data--pid-file=/mydata/data/master.pid
mysql 1270 0.1 9.8 1158028 100288 ? Sl 10:12 0:11/usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/mydata/data--plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/usr/local/mysql/log/mysql-error.log--pid-file=/mydata/data/master.pid --socket=/tmp/mysql.sock --port=3306
root 1381 0.0 0.2 198024 2360 pts/0 S+ 10:27 0:00 mysql -uroot -p
root 1447 0.0 0.0 103244 852 pts/1 S+ 12:37 0:00 grep mysql
mysql>SET GLOBAL SQL_SLAVE_SKIP_COUNTER= 2 #跳过一个事务
查看xfs是否挂载
[root@master ~]# lsmod |grep xfs
xfs 1128174 1
exportfs 4236 1 xfs
#开启半同步复制模式，每次重启mysql就不需要手动重新开启半同步复制模式master端?
rpl_semi_sync_master_enabled = 1
显示所有binlog
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 126 |
| mysql-bin.000002 | 1587 |
| mysql-bin.000003 | 421 |
| mysql-bin.000004 | 126 |
| mysql-bin.000005 | 126 |
| mysql-bin.000006 | 1949 |
| mysql-bin.000007 | 1839 |
| mysql-bin.000008 | 126 |
| mysql-bin.000009 | 670 |
+------------------+-----------+
9 rows in set (0.00 sec)
设置自动清理binlog
配置my.cnf：
expire_logs_days = 7
or
mysql>set global expire_logs_days = 7;
====================================================
常见理论问题
如何区分哪台是主，哪台是从？
====================================================
#show slave status\G信息解析
mysql> show slave status\G
*************************** 1. row***************************
Slave_IO_State: Waiting formaster to send event
Master_Host: 192.168.14.76
Master_User: master
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000009
主数据库的binlog的日志文件，当前slave_io正在读的文件
Read_Master_Log_Pos: 670
主服务器的pos点，当前slave_io已经读取到本地的点。上面两句就是说从服务器slave_io已经将主服务器
mysql-bin.000009，Pos: 670 及其以前的数据全部读到本地的Relay_Log_File里面了。
Relay_Log_File:slave-relay-bin.000002
从服务器，本机通过Slave_IO这个进程从主服务器啦过来的信息写入的日志文件，这个日志文件里面的信息来源于主服务器。"SQL线程当前正在读取和执行的中继日志文件的名称"。
Relay_Log_Pos: 816
这个pos点和上面的Read_Master_Log_Pos在数字上没有相关性,在新的中继日志里，这个数值会变小并重新增长，这里的pos是slave_io写日志时自己定义的
他们的关系是：mysql-bin.000009
at 816 的上一条记录里的主服务器pos点是816 ,如果终止slave,可以看到上面
Relay_Log_Pos的值在relay日志里是看不到的。它将是下一条relaylog的pos点（at值）
Relay_Master_Log_File: mysql-bin.000009
Slave_IO_Running: Yes
从主服务器拉取信息是否正常
Slave_SQL_Running: Yes
读取本机的mysql-bin.000009然后执行里面的文件是否正常
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table: test1.%,testa.%
Replicate_Wild_Ignore_Table:mysql.%,information_schema.%,performance_schema.%
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 670
执行到的主机的pos点。就是本机的slave_sql读取本机的中继日志，对应到的主服务器的pos点，所以上面的几个参数都是对于slave_io而言
Relay_Log_Space: 972
所有原有的中继日志结合起来的总大小
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: 763306
1 row in set (0.00 sec)
====================================================
#有关My.cnf文件解析
设置配置文件
在配置文件my.cnf中有一条语句：
[mysqld]
!include/home/mysql/mysql8892/etc/mysqld.cnf
表示包含/home/mysql/mysql8892/etc/mysqld.cnf文件的配置，所以只需要修改mysqld.cnf中的内容。
主服务器中mysqld.cnf的内容如下：
[mysqld]
# server-id必须是唯一的，在1 到 2^32 – 1之间取值
server-id = 210110051
# 二进制日志 – 主库必须开启
log-bin = mysql-bin-m
从服务器中mysqld.cnf的内容如下：
[mysqld]
server-id = 210110052
#在从库开启该选项，避免在从库上进行写操作，导致主从数据不一致（对super权限无效）
read-only
report-user = mysqlsync (从库连接主库使用该用户名，便于主库统计信息)
report-host = localhost (从库连接主库的主机名，便于主库统计信息)
report-port = 8893 (从库使用的端口号)
skip-slave-start (启动数据库后，需手动开启同步进程)
master-host = 127.0.0.1 （主库地址，必须使用IP，不能使用域名）
master-user = mysqlsync (从库连接主库使用该用户名)
master-port = 8892
relay-log = mysql-relay （中继日志，从库开启）
relay-log-info-file = mysql-relay.info（中继日志信息文件，默认为mysql-relay.info）
replicate-wild-do-table = example1.% （需要同步的表，支持正则表达式，数据库example1下的所有表）
replicate-do-table = example1.table1 (需要同步的表，多个表需多次指定，数据库example1下的table1)
# 同步过程中需要忽略的表，支持正则表达式。全库同步时，必须屏蔽mysql系统库和test测试库
replicate-wild-ignore-table = mysql.%
replicate-wild-ignore-table = test.%
replicate-rewrite-db =from_name->to_name (同步库重命名)
slave-net-timeout = 3600 (在所设置的时间内如果没有接收到来自主库的更新，从库则认为和主库的连接断开或失效，重新建立和主库的连接，默认为3600秒，可根据实际需求调整)
max_allowed_packet = 32M (任何生成的中间字符串的最大大小。默认为16M，最低配置32M，且不能小于主库该项的值)
read_only
该选项让从服务器只允许来自从服务器线程或具有SUPER权限的用户的更新。可以确保从服务器不接受来自客户的更新。
–replicate_do_db=db_name
告诉从服务器只做默认数据库(由USE所选择)为db_name的语句的复制。要指定多个数据库，应多次使用该选项，每个数据库使用一次。请注意不复制跨数据库的语句
–replicate_do_table=db_name.tbl_name
告诉从服务器线程只做对指定表的复制。要指定多个表，应多次使用该选项，每个表使用一次。同–replicate-do-db对比，允许跨数据库更新。
–replicate_ignore_db=db_name
告诉从服务器不要复制默认数据库(由USE所选择)为db_name的语句。要想忽略多个数据库，应多次使用该选项，每个数据库使用一次。
–replicate-ignore-table=db_name.tbl_name
告诉从服务器线程不要复制更新指定表的任何语句(即使该语句可能更新其它的表)。要想忽略多个表，应多次使用该选项，每个表使用一次。
–replicate_wild_do_table=db_name.tbl_name
告诉从服务器线程限制复制更新的表匹配指定的数据库和表名模式的语句。模式可以包含‘%'和‘_'通配符，与LIKE模式匹配操作符具有相同的含义。要指定多个表，应多次使用该选项，每个表使用一次。该选项可以跨数据库进行更新。
–replicate_wild_ignore_table=db_name.tbl_name
告诉从服务器线程不要复制表匹配给出的通配符模式的语句。要想忽略多个表，应多次使用该选项，每个表使用一次。该选项可以跨数据库进行更新。
–replicate_rewrite_db=from_name->to_name
告诉从服务器如果默认数据库(由USE所选择)为主服务器上的from_name，则翻译为to_name。只影响含有表的语句
–report_host=slave_name
从服务器注册过程中报告给主服务器的主机名或IP地址。该值出现在主服务器上SHOW SLAVE HOSTS的输出中。如果不想让从服务器自己在主服务器上注册，则不设置该值。
–report_port=slave_port
连接从服务器的TCP/IP端口号，从服务器注册过程中报告给主服务器。
–skip_slave_start
告诉从服务器当服务器启动时不启动从服务器线程。使用START SLAVE语句在以后启动线程。
–slave_skip_errors=[err_code1,err_code2,… | all]
通常情况，当出现错误时复制停止，这样给你一个机会手动解决数据中的不一致性问题。该选项告诉从服务器SQL线程当语句返回任何选项值中所列的错误时继续复制。
例如：
–slave-skip-errors=1062,1053
–slave-skip-errors=all
===============================================
六、主从切换
主库master 192.168.14.76
从库 slave 192.168.14.111
===============================================
1、修改配置文件
[root@master ~]# vi /etc/my.cnf #主库添加
read-only = 1
[root@slave ~]# vi /etc/my.cnf #从库隐去下面参数
#read-only = 1
==============================================
2、查询从库状态
mysql> show processlist ;
mysql> show processlist ;
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
| 1| system user | | NULL |Connect | 1536 | Slave has read all relay log; waiting for the slave I/O threadto update it | NULL |
| 2| system user | | NULL | Connect| 1537 | Waiting for master to send event |NULL |
| 3| root | localhost | NULL |Query | 0 | NULL | show processlist |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
-----------------------------------------------
mysql> show slave status \G
*************************** 1. row***************************
Slave_IO_State: Waiting formaster to send event
Master_Host: 192.168.14.76
Master_User: master
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000010
Read_Master_Log_Pos: 107
Relay_Log_File:slave-relay-bin.000005
Relay_Log_Pos: 253
Relay_Master_Log_File: mysql-bin.000010
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table: test1.%,testa.%
Replicate_Wild_Ignore_Table:mysql.%,information_schema.%,performance_schema.%
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 107
Relay_Log_Space: 555
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: 763306
1 row in set (0.00 sec)
===============================================
3、查询主库状态
mysql> show processlist;
mysql> show processlist;
+----+--------+----------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+--------+----------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+
| 1| master | 192.168.14.111:59135 | NULL | Binlog Dump | 1608 | Master has sentall binlog to slave; waiting for binlog to be updated | NULL |
| 2| root | localhost | NULL | Query | 0 | NULL | show processlist |
+----+--------+----------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+
2 rows in set (0.00 sec)
------------------------------------------------
mysql> show master status \G
*************************** 1. row***************************
File: mysql-bin.000010
Position: 107
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)
================================================
4、从库操作
mysql> STOP SLAVE IO_THREAD;
Query OK, 0 rows affected (0.04 sec
------------------------------------------------
mysql> SHOW PROCESSLIST;
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
| 1| system user | | NULL |Connect | 1717 | Slave has read all relay log; waiting for the slave I/O threadto update it | NULL |
| 3| root | localhost | NULL |Query | 0 | NULL |SHOW PROCESSLIST |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
2 rows in set (0.00 sec)
------------------------------------------------
mysql> show slave status \G
*************************** 1. row***************************
Slave_IO_State:
Master_Host: 192.168.14.76
Master_User: master
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000010
Read_Master_Log_Pos: 107
Relay_Log_File:slave-relay-bin.000005
Relay_Log_Pos: 253
Relay_Master_Log_File: mysql-bin.000010
Slave_IO_Running: No
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table: test1.%,testa.%
Replicate_Wild_Ignore_Table:mysql.%,information_schema.%,performance_schema.%
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 107
Relay_Log_Space: 555
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: 763306
1 row in set (0.00 sec)
--------------------------------------------------
5、查询主库状态
mysql> show master status \G
*************************** 1. row***************************
File: mysql-bin.000010
Position: 107
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)
----------------------------------------------------
6、从库变主库
mysql> STOP SLAVE;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status \G
*************************** 1. row***************************
Slave_IO_State:
Master_Host: 192.168.14.76
Master_User: master
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000010
Read_Master_Log_Pos: 107
Relay_Log_File:slave-relay-bin.000005
Relay_Log_Pos: 253
Relay_Master_Log_File: mysql-bin.000010
Slave_IO_Running: No
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table: test1.%,testa.%
Replicate_Wild_Ignore_Table:mysql.%,information_schema.%,performance_schema.%
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 107
Relay_Log_Space: 555
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: 763306
1 row in set (0.00 sec)
---------------------------------------------
mysql> RESET MASTER;
Query OK, 0 rows affected (0.07 sec)
---------------------------------------------
mysql> RESET SLAVE;
Query OK, 0 rows affected (0.07 sec)
---------------------------------------------
mysql> show master status \G
*************************** 1. row***************************
File: mysql-bin.000001
Position: 107
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)
----------------------------------------------
7、主库变从库
mysql> RESET MASTER;
Query OK, 0 rows affected (0.11 sec)
----------------------------------------------
mysql> RESET SLAVE;
Query OK, 0 rows affected (0.00 sec)
-----------------------------------------------
mysql> CHANGE MASTER TOMASTER_HOST='192.168.14.111',
-> MASTER_USER='master',
-> MASTER_PASSWORD='oracle',
-> MASTER_LOG_FILE='mysql-bin.000001',
-> MASTER_LOG_POS=107;
Query OK, 0 rows affected (0.05 sec)
-------------------------------------------------
mysql> start slave;
Query OK, 0 rows affected (0.02 sec)
-------------------------------------------------
8、重启主和从库
[root@master ~]# service mysqld restart
Shutting down MySQL. SUCCESS!
Starting MySQL.. SUCCESS!
=================================================
9、检查主从是否都正常
主库
mysql> SHOW PROCESSLIST;
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------+------------------+
| 1| root | localhost | NULL | Query | 0 | NULL | SHOW PROCESSLIST |
+----+------+-----------+------+---------+------+-------+------------------+
1 row in set (0.00 sec)
-------------------------------------------------
mysql> show master status \G
*************************** 1. row***************************
File: mysql-bin.000002
Position: 107
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)
==================================================
从库
mysql> SHOW PROCESSLIST;
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
| 1| system user | | NULL |Connect | 118 | Slave has read all relaylog; waiting for the slave I/O thread to update it | NULL |
| 2| system user | | NULL | Connect | 118 | Reconnecting after a failed masterevent read | NULL |
| 3| root | localhost | NULL |Query | 0 | NULL | SHOW PROCESSLIST |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
3 rows in set (0.00 sec)
---------------------------------------------------
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.00sec)
---------------------------------------------------
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.00sec)
mysql> show slave status \G
*************************** 1. row***************************
Slave_IO_State: Waiting formaster to send event
Master_Host: 192.168.14.111
Master_User: master
Master_Port: 3306
Connect_Retry: 60
Master_Log_File:mysql-bin.000002
Read_Master_Log_Pos: 107
Relay_Log_File:master-relay-bin.000005
Relay_Log_Pos: 253
Relay_Master_Log_File: mysql-bin.000002
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: 107
Relay_Log_Space: 556
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: 1113306
1 row in set (0.00 sec)
===============================================
主库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test1 |
| testa |
+--------------------+
5 rows in set (0.02 sec)
-------------------------------------------------
从库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test1 |
| testa |
+--------------------+
5 rows in set (0.03 sec)
====================================================
测试转换后的备库是否可以接收insert,update ,delete,create table
主库插入了数据
mysql> select * from t1;
+----+------+
| id | name |
+----+------+
| 0| aaa |
| 1| b |
| 2| c |
| 3| bbb |
+----+------+
4 rows in set (0.00 sec)
从库也可以得到这些信息
mysql> select * from t1;
+----+------+
| id | name |
+----+------+
| 0| aaa |
| 1| b |
| 2| c |
| 3| bbb |
+----+------+
======================================================
update
mysql> update t1 set name='ddd' where id = 3;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t1;
+----+------+
| id | name |
+----+------+
| 0| aaa |
| 1| b |
| 2| c |
| 3| ddd |
| 4| ccc |
+----+------+
5 rows in set (0.00 sec)
从库接收查看
mysql> select * from t1;
+----+------+
| id | name |
+----+------+
| 0| aaa |
| 1| b |
| 2| c |
| 3| ddd |
| 4| ccc |
+----+------+
5 rows in set (0.00 sec)
==================================================
delete
主库执行delete
mysql> delete from t1 where id = '4';
Query OK, 1 row affected (0.04 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t1;
+----+------+
| id | name |
+----+------+
| 0| aaa |
| 1| b |
| 2| c |
| 3| ddd |
+----+------+
4 rows in set (0.00 sec)
备库接收
mysql> select * from t1;
+----+------+
| id | name |
+----+------+
| 0| aaa |
| 1| b |
| 2| c |
| 3| ddd |
+----+------+
4 rows in set (0.00 sec)
====================================================
create table
主库创建信息
mysql> CREATE TABLE t2(id int not nullprimary key,name char(20));
Query OK, 0 rows affected (0.03 sec)
mysql> insert into t2(id,name)values('1','aaa');
Query OK, 1 row affected (0.02 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t2;
+----+------+
| id | name |
+----+------+
| 1| aaa |
+----+------+
1 row in set (0.00 sec)
备库接收信息
mysql> use testa;
Database changed
mysql> select * from t2;
+----+------+
| id | name |
+----+------+
| 1| aaa |
+----+------+
1 row in set (0.00 sec)
OK,成功
#文章中部分信息整理自网络
扫一扫
837
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
