mysql 双机主主互备搭建实践

mysql 双机主主互备搭建实践

1:环境及版本

centos6.7 + mysql-5.7.6
ip1:192.168.1.201
ip2:192.168.1.202

2:安装

1:环境检查

安装前先检查一下系统是否安装过mysql

[root@snails ~]# yum list installed | grep mysql
[root@snails ~]# ps -ef|grep mysql
[root@snails ~]# service mysqld stop 
[root@snails ~]# rpm -e mysql-libs --nodeps
[root@snails ~]# yum -y remove mysql mysql-*

2:设置新的安装源

[root@snails ~]# wget http://repo.mysql.com/mysql57-community-release-el6-8.noarch.rpm
[root@snails ~]# rpm -ivh mysql57-community-release-el6-8.noarch.rpm
[root@snails ~]# ls -1 /etc/yum.repos.d/mysql-community*
[root@snails ~]# yum repolist all | grep mysql
[root@snails ~]# vi /etc/yum.repos.d/mysql-community.repo
### 将[mysql56-community]的enabled设置为１,[mysql57-community]的enabled设置为0 ### 
[root@snails ~]# yum repolist enabled | grep mysql
mysql-connectors-community MySQL Connectors Community           21
mysql-tools-community      MySQL Tools Community                37
mysql56-community          MySQL 5.6 Community Server          265

3:安装mysql

[root@snails ~]# yum -y install mysql-server mysql

3:修改配置

3.1创建数据目录

#makedir -p /data/mysql
[root@v3b-stest-13 mysql]# pwd
/data/mysql

3.2修改默认配置

mysql默认安装后配置文件在目录/etc下,双机主主配置，两台机器都需要修改以下配置
修改my.conf 配置如下:

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
#datadir=/var/lib/mysql
#socket=/var/lib/mysql/mysql.sock

#########change by noahli##########
#机器id 使用机器的host末位数字
server-id=201
#启用二进制日志
log-bin=mysql-bin
#忽略写入binlog的库
binlog-ignore-db=mysql,information_schema

#只同步abc库
replicate-do-db=collectionGod
##忽略因复制出现的所有错误
slave-skip-errors=all
#########change by noahli##########

datadir=/data/mysql
socket=/tmp/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[client]
socket=/tmp/mysql.sock

3.3 添加mysql账户

mysql> select host,user from mysql.user;
+------------------------------+--------+
| host                         | user   |
+------------------------------+--------+
| %                            | noahli |
| 192.168.1.202                 | noahli |
| 127.0.0.1                    | root   |
| ::1                          | root   |
| localhost                    |        |
| localhost                    | noahli |
| localhost                    | root   |
| v3b-stest-13.201.localdomain |        |
| v3b-stest-13.201.localdomain | root   |
+------------------------------+--------+
9 rows in set (0.00 sec)
#创建一个数据库
mysql> create database collectionGod;
#创建一个账户(需要远程登录)
mysql> create user noahli identified by '****';
#为新创建的用户分配权限
mysql> grant all privileges on *.* to noahli@'%' identified by '****';
#刷新用户权限相关表
mysql> flush privileges;

4:修改master,slave服务器(双机互为master和slave)

4.1 启动数据库

[root@v3b-stest-13 mysql]# service mysqld start
Starting mysqld (via systemctl):                         [  OK  ]
#查看主状态
mysql> show master status;
+------------------+----------+--------------+--------------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB         | Executed_Gtid_Set |
+------------------+----------+--------------+--------------------------+-------------------+
| mysql-bin.000001 |      432 |              | mysql,information_schema |                   |
+------------------+----------+--------------+--------------------------+-------------------+
1 row in set (0.00 sec)

#配置从数据库 ps:从数据库配置信息来源于对应的主数据库的状态，也就是在另一台机器上执行show master status后查询出来的数据 注意以下命令的参数是来自于另一台数据库的status

mysql> change master to 
master_host='192.168.1.202',
master_user='noahli',
master_password='*****',
master_log_file='mysql-bin.000001',
master_log_pos=120;

#启动slave 
mysql> start slave;
#查看slave状态 ps:Slave_IO_Running 和 Slave_SQL_Running都为yes配置才是成功的
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.202
                  Master_User: noahli
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 2148
               Relay_Log_File: mysqld-relay-bin.000006
                Relay_Log_Pos: 1307
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: collectionGod
          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: 2148
              Relay_Log_Space: 1481
              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: 202
                  Master_UUID: 3401c993-46ae-11e7-a516-525400dda787
             Master_Info_File: /data/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
1 row in set (0.00 sec)

ERROR: 
No query specified