mysql 主从复制和主主复制配置

mysql 主从复制和主主复制配置

按照单机模式去部署。

在安装时，可能会遇到报错：
启动mysql 报错

[mysqladmin@dg2 ~]$ mysqlmysql: error while loading shared libraries: libmysqlclient.so.16: cannot open shared object file: No such file or directory解决办法yum reinstall mysql-libs -y 

在此之前我们必须要保证每台MySQL服务器里的数据同步。
数据同步我们可以通过MySQL内部配置就可以轻松完成，主要有主从复制和主主复制。

两台linux虚拟主机
Linux版本CentOS6.6、MySQL 5.5
ip：192.168.4.11、192.168.4.12

主主复制即在两台MySQL主机内都可以变更数据，而且另外一台主机也会做出相应的变更。
就是将两个主从复制有机合并起来就好了。
只不过在配置的时候我们需要注意一些问题，例如，主键重复，server-id不能重复等等。

--------------192.168.4.11--------------#新增的配置#server-id  = 11#log-bin=/usr/local/mysql/arch/mysql-binauto_increment_increment=2auto_increment_offset=1replicate-do-db=aa--------------192.168.4.12--------------#新增的配置#server-id  = 12#log-bin=/usr/local/mysql/arch/mysql-binauto_increment_increment=2auto_increment_offset=2replicate-do-db=aa

============先构建主从，主11 从12 ===============

第一步：
在192.168.4.11中创建一个192.168.4.12主机中可以登录的MySQL用户【在2台上都要建】

用户：mysql12
密码：mysql12

CREATE USER mysql12 IDENTIFIED BY 'mysql12';mysql>GRANT REPLICATION SLAVE ON *.* TO 'mysql12'@'%' IDENTIFIED BY 'mysql12';mysql>FLUSH PRIVILEGES;

第二步：
查看192.168.4.11 MySQL服务器二进制文件名与位置

mysql> SHOW MASTER STATUS;+------------------+----------+--------------+------------------+-------------------+| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+------------------+-------------------+| mysql-bin.000004 |      589 |              |                  |                   |+------------------+----------+--------------+------------------+-------------------+1 row in set (0.00 sec)

第三步：
告知二进制文件名与位置
在192.168.4.12中执行：

CREATE USER mysql12 IDENTIFIED BY 'mysql12';GRANT REPLICATION SLAVE ON *.* TO 'mysql12'@'%' IDENTIFIED BY 'mysql12';FLUSH PRIVILEGES;

mysql>CHANGE MASTER TOMASTER_HOST='192.168.4.11',MASTER_USER='mysql12',MASTER_PASSWORD='mysql12',MASTER_LOG_FILE='mysql-bin.000004',MASTER_LOG_POS=589;

结果：

mysql> CHANGE MASTER TO    -> MASTER_HOST='192.168.4.11',    -> MASTER_USER='mysql12',    -> MASTER_PASSWORD='mysql12',    -> MASTER_LOG_FILE='mysql-bin.000004',    -> MASTER_LOG_POS=589;Query OK, 0 rows affected, 2 warnings (0.58 sec)

完成主从复制配置

测试主从复制
在192.168.4.12中

mysql> start slave;   #开启复制Query OK, 0 rows affected (0.13 sec)   

mysql> show slave status \G;   #查看主从复制是否配置成功*************************** 1. row ***************************               Slave_IO_State: Waiting for master to send event                  Master_Host: 192.168.4.11                  Master_User: mysql12                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: mysql-bin.000004          Read_Master_Log_Pos: 589               Relay_Log_File: hostname-relay-bin.000002                Relay_Log_Pos: 283        Relay_Master_Log_File: mysql-bin.000004             Slave_IO_Running: Yes            Slave_SQL_Running: Yes 

====192.168.4.11上mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema || test               |+--------------------+4 rows in set (0.04 sec)mysql> create database aa;Query OK, 1 row affected (0.08 sec)mysql> use aa;Database changedmysql> create table  tab1(id int,name varchar(10),primary key(id));Query OK, 0 rows affected (0.43 sec)mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || aa                 || mysql              || performance_schema || test               |+--------------------+5 rows in set (0.00 sec)mysql> show tables;+--------------+| Tables_in_aa |+--------------+| tab1         |+--------------+1 row in set (0.00 sec)mysql> mysql> mysql> create database ab;Query OK, 1 row affected (0.00 sec)mysql> use ab;mysql> create table t1 (id int, name varchar(10),address varchar(10));Query OK, 0 rows affected (0.13 sec)mysql> insert into t1 values (1,'wxk','NJ');Query OK, 1 row affected (0.09 sec)         

======192.168.4.12上         mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || aa                 || mysql              || performance_schema || test               |+--------------------+5 rows in set (0.00 sec)mysql> use aa;Database changedmysql> show tables ;+--------------+| Tables_in_aa |+--------------+| tab1         |+--------------+1 row in set (0.00 sec)mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || aa                 || ab                 || mysql              || performance_schema || test               |+--------------------+6 rows in set (0.00 sec)mysql> use ab;Database changedmysql> show tables ;+--------------+| Tables_in_ab |+--------------+| t1           |+--------------+1 row in set (0.00 sec)mysql> select * from t1;+------+------+---------+| id   | name | address |+------+------+---------+|    1 | wxk  | NJ      |+------+------+---------+1 row in set (0.00 sec)         

============上面是主从复制+测试， 下面是主主复制+测试，主主复制接着主从复制================
因为主主复制是两个主从复制组合一起，所以我就接着上面主从复制接着配置。

第一步：

在192.168.95.12中创建一个192.168.95.11主机中可以登录的MySQL用户【在2台上都要建】
用户：mysql11
密码：mysql11

CREATE USER mysql11 IDENTIFIED BY 'mysql11';GRANT REPLICATION SLAVE ON *.* TO 'mysql11'@'%' IDENTIFIED BY 'mysql11';FLUSH PRIVILEGES;

第二步：
在192.168.95.12查看二进制日志名和位置

mysql> show master status ;+------------------+----------+--------------+------------------+-------------------+| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+------------------+-------------------+| mysql-bin.000005 |     1012 |              |                  |                   |+------------------+----------+--------------+------------------+-------------------+1 row in set (0.00 sec)

第三步：
告知二进制文件名与位置
在192.168.95.11中执行：

CREATE USER mysql11 IDENTIFIED BY 'mysql11';GRANT REPLICATION SLAVE ON *.* TO 'mysql11'@'%' IDENTIFIED BY 'mysql11';FLUSH PRIVILEGES;mysql>CHANGE MASTER TOMASTER_HOST='192.168.4.12',MASTER_USER='mysql11',MASTER_PASSWORD='mysql11',MASTER_LOG_FILE='mysql-bin.000005',MASTER_LOG_POS=1012;

完成主主复制配置

测试主主复制

mysql> start slave;Query OK, 0 rows affected (0.02 sec)

#操作时，在11上发现，Slave_IO_Running: Connecting 是有问题的！！！#一般有两个原因#1. 网络原因：在从库上拷贝主库上的备份文件的时候并没有出现任何的错误，因此可以排除。  #2. POS 不对：这个我也可以排除，因为在主库上 show maste status 时我记录过了 POS，所以也将这个原因排除了。 #经过排查发现，是在配置11的时候，把mysql11写成了mysql1 导致用户名密码错误。#先停止 stop slave; 重新刷入配置，然后启动 start slave slave;#结果正常！

mysql> show slave status \G;*************************** 1. row ***************************               Slave_IO_State: Connecting to master                  Master_Host: 192.168.4.12                  Master_User: mysql1                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: mysql-bin.000005          Read_Master_Log_Pos: 1012               Relay_Log_File: hostname-relay-bin.000001                Relay_Log_Pos: 4        Relay_Master_Log_File: mysql-bin.000005             Slave_IO_Running: Yes            Slave_SQL_Running: Yes

测试：

============12上创建测试数据：mysql> create database ac;Query OK, 1 row affected (0.01 sec)mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || aa                 || ab                 || ac                 || mysql              || performance_schema || test               |+--------------------+7 rows in set (0.00 sec)mysql> use ac;Database changedmysql> create table t2 (id int ,name varchar(10));Query OK, 0 rows affected (0.02 sec)mysql> insert into t2 values (1,'panda');Query OK, 1 row affected (0.00 sec)============在11上检查mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || aa                 || ab                 || ac                 || mysql              || performance_schema || test               |+--------------------+7 rows in set (0.00 sec)mysql> use ac;Database changedmysql> show tables ;+--------------+| Tables_in_ac |+--------------+| t2           |+--------------+1 row in set (0.00 sec)mysql> select * from t2;+------+-------+| id   | name  |+------+-------+|    1 | panda |+------+-------+1 row in set (0.00 sec)

注意事项
1、主主复制配置文件中auto_increment_increment和auto_increment_offset只能保证主键不重复，却不能保证主键有序。

2、当配置完成Slave_IO_Running、Slave_SQL_Running不全为YES时，show slave status\G信息中有错误提示，可根据错误提示进行更正。

3、Slave_IO_Running、Slave_SQL_Running不全为YES时，大多数问题都是数据不统一导致。

常见出错点：
1、两台数据库都存在db数据库，而第一台MySQL db中有tab1，第二台MySQL db中没有tab1，那肯定不能成功。
2、已经获取了数据的二进制日志名和位置，又进行了数据操作，导致POS发生变更。在配置CHANGE MASTER时还是用到之前的POS。
3、stop slave后，数据变更，再start slave。出错。

终极更正法：

1、配置锁定，导出所有库mysql>FLUSH TABLES WITH READ LOCK系统命令行--导出所有库mysqldump -uroot -ppassword --all-databases > all.sql传到备库上,/user/local/mysql 目录下($MYSQL_BASE)2 导入所有库mysql命令行mysql>source all.sql;3、重新执行一遍CHANGE MASTER就好了。3.1 第一台上执行  mysql> show master status ;3.2 第二台上执行  mysql>CHANGE MASTER TOMASTER_HOST='192.168.4.12',MASTER_USER='mysql11',MASTER_PASSWORD='mysql11',MASTER_LOG_FILE='mysql-bin.000005',  //修改MASTER_LOG_POS=1012;                 //修改4、解锁把第一个界面关闭即可，或者输入 mysql> unlock tables。=================

测试结果：
所有在 mysql 界面的操作都会在两端执行，包括ddl ，dml。

主键的坑要继续看！！！！