mysql-5.5配置主从 及 主主关系

378

　　前言：Mysql在生产环境中的宕机率特别的高，一般我们在该机器上只安装mysql，不作他用。数据库本     身也是极易产生瓶颈的地方。Master：主（用于写入数据） Slave：从（用于读取数据），也是     实现读写分离的重要举措。国内很多的视频直播网站、bilibili等弹幕类的网站，在视频框架上     做了个透明的弹幕，       后台数据库每秒发生数以万计的读写操作，后台运维人员真是想     死的心都有。。。哈哈。。废话不多说，开始部署……
　　下面开始配置主从关系，配置完恢复快照到mysql编译好的环境继续主主关系的配置
　　准备前的步骤：关闭selinux，关闭防火墙、已编译好的mysql5.5
　　实验环境：Centos6.5 IP：192.168.1.1 Mysql主
　　Centos6.5 IP：192.168.1.2 Mysql从
　　配置主
　　[root@localhost ~]# mysql -u root -predhat  //mysql登录用户及密码
　　mysql> show dabaseses;  //显示数据库
　　mysql> use mysql;    //使用mysql库
　　mysql> show tables;   //显示表
　　mysql> desc user;    //查看user表的结构
　　mysql> select Host,User,Password from mysql.user; //在user表中查询主机，用户密码字段
　　mysql> insert into mysql.user(Host,User,Password) values('localhost','baidu',password('123.com'));  //插入一条数据，用户百度，密码123.com

　　mysql> grant replication slave on *.* to 'baidu'@'192.168.1.2'>　　mysql> flush privileges;   //刷新授权
　　mysql> create database testdb; //创建测试的库，库名为testdb
　　[root@localhost ~]# vim /etc/my.cnf
　　大约49行log-bin=mysql-bin处
　　添加：binlog-do-db=testdb       //将test库写入二进制日志
　　binlog-ignore-db=mysql   //忽视mysql库写入二进制日志

　　修改：server-id = 1    每台做主从或主主的server>　　[root@localhost ~]# /etc/init.d/mysqld restart
　　[root@localhost ~]# mysql -u root -predhat
　　mysql> show master status;
　　+------------------+----------+--------------+------------------+
　　| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
　　+------------------+----------+--------------+------------------+
　　| mysql-bin.000005 |      107 | testdb         | mysql            |
　　+------------------+----------+--------------+------------------+
　　1 row in set (0.00 sec) //上面两个值将在从Mysql中用到。。
　　下面配置从mysql
　　[root@localhost ~]# vim /etc/my.cnf
　　大约49行log-bin=mysql-bin处
　　添加：replicate-do-db=testdb    //复制的库名testdb
　　replicate-ignore-db=mysql //不复制的库名mysql
　　修改：server-id = 2    值唯一
　　[root@localhost ~]# /etc/init.d/mysqld restart
　　[root@localhost ~]# mysql -u root -predhat
　　mysql> create database testdb; //创建和主上面一样的同步的库名
　　mysql> lave stop;
　　mysql> change master to master_host='192.168.1.1', master_user='baidu', master_password='123.com', master_log_file='mysql-bin.000005', master_log_pos=107;
　　//指定主的IP，可复制用户及密码。上面两个值在每次重启主Mysql都会变。如果重启，则需要重新change master to 了
　　mysql> slave start;  //开启复制
　　mysql> show slave status\G
　　mysql> show slave status\G
　　*************************** 1. row ***************************
　　Slave_IO_State: Waiting for master to send event
　　Master_Host: 192.168.1.1
　　Master_User: baidu
　　Master_Port: 3306
　　Connect_Retry: 60
　　Master_Log_File: mysql-bin.000005
　　Read_Master_Log_Pos: 107
　　Relay_Log_File: localhost-relay-bin.000003

　　>
　　>　　Slave_IO_Running: Yes
　　Slave_SQL_Running: Yes
　　Replicate_Do_DB: testdb
　　Replicate_Ignore_DB: mysql
　　两个Yes表示主从配置正确……
　　接下来我们到主mysql的test库上面创建一个表，再到从mysql上面看看是否同步过来了。
　　主mysql
　　mysql> use testdb;
　　Database changed
　　mysql> create table student(id int(11) not null, primary key(id));
　　Query OK, 0 rows affected (0.06 sec)
　　mysql> show tables;
　　+----------------+
　　| Tables_in_user |
　　+----------------+
　　| student        |
　　+----------------+
　　1 row in set (0.00 sec)
　　从mysql
　　mysql> use testdb;
　　Database changed
　　mysql> show tables;
　　+----------------+
　　| Tables_in_user |
　　+----------------+
　　| student        |
　　+----------------+
　　1 row in set (0.00 sec)
　　可以看到student表已经成功同步过来了，至此Mysql主从实验已完成。
　　PS：常见错误，防火墙阻塞了端口，授权密码填写错误   mysql-bin错误    每次重启服务会变更        change mater to   需要重新敲。
　　Mysql编译安装的都有一个小小的BUG，不管什么错误导致mysql重启失败都会报PID丢失，一般都是你的配置文件写错了，或者查找日志文件进行排错。。
　　下面开始Mysql的主主关系配置：会了上面的主从之后，就很简单了。实际上就是互相指定主就可以了，你是我的主，又是我的从。
　　实验环境：Centos6.5 192.168.1.1  Master1：授权给1.2的用户为baidu
　　Centos6.5 192.168.1.2   Master2：授权给1.1的用户为sina
　　1.1上面操作：↓
　　mysql> create database testdb;
　　Query OK, 1 row affected (0.00 sec)
　　mysql> insert into mysql.user(Host,User,Password) values('localhost','baidu',password('123.com'));
　　Query OK, 1 row affected, 3 warnings (0.01 sec)

　　mysql> grant replication slave on *.* to 'baidu'@'192.168.1.2'>　　Query OK, 0 rows affected (0.00 sec)
　　mysql> flush privileges;
　　Query OK, 0 rows affected (0.00 sec)
　　[root@localhost ~]# vim /etc/my.cnf
　　大约49行log-bin=mysql-bin处
　　添加：binlog-do-db=testdb   //开启testdb二进制日志
　　binlog-ignore-db=mysql //忽视开启mysql二进制日志
　　replicate-do-db=testdb //复制的表
　　replicate-ignore-db=mysql //不复制的表
　　auto-increment-increment=10 //自增的值
　　auto-increment-offset=1   //原值
　　log-slave-updates=on  //开启从日志更新
　　修改：server-id = 1   //该值应唯一，与另一台不冲突
　　上面自增与原值的解释：为了防止mysql写入数据不冲突而设立，可以这样理解，1.1写入数据按照1，11，21，31，41，
　　1.2按照2，12，22，32，42   这样就不会产生冲突了
　　修改完配置文件要重启mysql服务
　　[root@localhost ~]# /etc/init.d/mysqld restart
　　1.2上面操作：↓
　　mysql> create database testdb;
　　Query OK, 1 row affected (0.00 sec)
　　mysql> insert into mysql.user(Host,User,Password) values('localhost','sina',password('123.com'));
　　Query OK, 1 row affected, 3 warnings (0.01 sec)

　　mysql> grant replication slave on *.* to 'sina'@'192.168.1.1'>　　Query OK, 0 rows affected (0.00 sec)
　　mysql> flush privileges;
　　Query OK, 0 rows affected (0.00 sec)
　　[root@localhost ~]# vim /etc/my.cnf
　　大约49行log-bin=mysql-bin处
　　添加：binlog-do-db=testdb
　　binlog-ignore-db=mysql
　　replicate-do-db=testdb
　　replicate-ignore-db=mysql
　　auto-increment-increment=10
　　auto-increment-offset=2
　　log-slave-updates=on
　　修改：server-id = 2
　　修改完配置文件要重启mysql服务
　　[root@localhost ~]# /etc/init.d/mysqld restart
　　1.1上面
　　mysql> show master status;
　　+------------------+----------+--------------+------------------+
　　| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
　　+------------------+----------+--------------+------------------+
　　| mysql-bin.000005 |      107 | testdb       | mysql            |
　　+------------------+----------+--------------+------------------+
　　1 row in set (0.00 sec)
　　mysql> slave stop;
　　Query OK, 0 rows affected, 1 warning (0.00 sec)
　　1.2上面
　　mysql> show master status;
　　+------------------+----------+--------------+------------------+
　　| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
　　+------------------+----------+--------------+------------------+
　　| mysql-bin.000004 |      107 | testdb       | mysql            |
　　+------------------+----------+--------------+------------------+
　　1 row in set (0.00 sec)
　　mysql> slave stop;
　　Query OK, 0 rows affected, 1 warning (0.00 sec)
　　下面将是主主配置的最后一步！
　　1.1上面
　　mysql> change master to master_host='192.168.1.2', master_user='sina', master_password='123.com', master_log_file='mysql-bin.000004', master_log_pos=107;
　　Query OK, 0 rows affected (0.06 sec)
　　1.2上面
　　change master to master_host='192.168.1.1', master_user='baidu', master_password='123.com', master_log_file='mysql-bin.000005', master_log_pos=107;
　　Query OK, 0 rows affected (0.07 sec)
　　两台都打上这条命令，开启从复制
　　mysql>slave start;
　　Query OK, 0 rows affected (0.01 sec)
　　两台都show slave status一下
　　mysql> show slave status\G
　　*************************** 1. row ***************************
　　Slave_IO_State: Waiting for master to send event
　　Master_Host: 192.168.1.2
　　Master_User: sina
　　Master_Port: 3306
　　Connect_Retry: 60
　　Master_Log_File: mysql-bin.000004
　　Read_Master_Log_Pos: 107
　　Relay_Log_File: localhost-relay-bin.000002

　　>
　　>　　Slave_IO_Running: Yes
　　Slave_SQL_Running: Yes
　　Replicate_Do_DB: testdb
　　Replicate_Ignore_DB: mysql
　　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

　　>　　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: 2
　　1 row in set (0.00 sec)
　　两个YES表示成功；
　　接下来测试；
　　1.1上面
　　mysql> use testdb;
　　Database changed
　　mysql> create table user(id int (16) not null, primary key (id));
　　Query OK, 0 rows affected (0.03 sec)
　　1.2上面use testdb；
　　show tables； 可以看到user表同步过来了，反之在1.2上面创建一个表，到1.1上面也能够看到同步成功
　　觉得博主写的好的收藏一下，手敲不容易。。