一、前言
在centos6.7的linux操作系统环境下通过部署两台mysql主机服务器（一台master主机、另一台slave备机）实现mysql（mysql57-community-release-el6-9.noarch.rpm(本地下载)安装参见其他文章页面）主从集群cluster的双机热备，详细配置步骤整理如下。
二、操作步骤
1.准备两台centos6.7的服务器分别安装mysql5版本（mysql57-community-release-el6-9.noarch.rpm安装）
2. 配置master主机（192.168.1.204），详细如下
原版本
[root@centos-cluster-s19423 soft]# cat /etc/my.cnf.bak2019513 @b@# For advice on how to change settings please see@b@# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html@b@@b@[mysqld]@b@#@b@# Remove leading # and set to the amount of RAM for the most important data@b@# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.@b@# innodb_buffer_pool_size = 128M@b@#@b@# Remove leading # to turn on a very important data integrity option: logging@b@# changes to the binary log between backups.@b@# log_bin@b@#@b@# Remove leading # to set options mainly useful for reporting servers.@b@# The server defaults are faster for transactions and fast SELECTs.@b@# Adjust sizes as needed, experiment to find the optimal values.@b@# join_buffer_size = 128M@b@# sort_buffer_size = 2M@b@# read_rnd_buffer_size = 2M@b@datadir=/var/lib/mysql@b@socket=/var/lib/mysql/mysql.sock@b@@b@# Disabling symbolic-links is recommended to prevent assorted security risks@b@symbolic-links=0@b@@b@log-error=/var/log/mysqld.log@b@pid-file=/var/run/mysqld/mysqld.pid@b@@b@@b@@b@@b@skip-grant-tables
修改后(改完重启mysql)
[root@centos-cluster-s19423 soft]# vi /etc/my.cnf@b@# For advice on how to change settings please see@b@# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html@b@@b@[mysqld]@b@#@b@# Remove leading # and set to the amount of RAM for the most important data@b@# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.@b@# innodb_buffer_pool_size = 128M@b@#@b@# Remove leading # to turn on a very important data integrity option: logging@b@# changes to the binary log between backups.@b@# log_bin@b@#@b@# Remove leading # to set options mainly useful for reporting servers.@b@# The server defaults are faster for transactions and fast SELECTs.@b@# Adjust sizes as needed, experiment to find the optimal values.@b@# join_buffer_size = 128M@b@# sort_buffer_size = 2M@b@# read_rnd_buffer_size = 2M@b@datadir=/var/lib/mysql@b@socket=/var/lib/mysql/mysql.sock@b@@b@server-id=1@b@@b@log-bin=mysql-bin@b@@b@expire_logs_days=7@b@@b@max_binlog_size=100m@b@@b@binlog_cache_size=4m@b@@b@max_binlog_cache_size=512m@b@@b@#需要同步的数据库名称，与slave上的replicate-do-db配置保持一致。 @b@binlog-do-db=mytest@b@@b@lower_case_table_names=1@b@@b@# Disabling symbolic-links is recommended to prevent assorted security risks@b@symbolic-links=0@b@@b@log-error=/var/log/mysqld.log@b@pid-file=/var/run/mysqld/mysqld.pid@b@@b@@b@@b@@b@skip-grant-tables@b@skip-name-resolve@b@@b@"/etc/my.cnf" 69L, 1212C@b@@b@[root@centos-cluster-s19423 soft]# service mysqld restart
[root@centos-cluster-s19423 soft]# mysql -uroot -p123456@b@.@b@mysql> show master status;@b@+------------------+----------+--------------+------------------+-------------------+@b@| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |@b@+------------------+----------+--------------+------------------+-------------------+@b@| mysql-bin.000007 | 1581 | mytest | | |@b@+------------------+----------+--------------+------------------+-------------------+@b@1 row in set (0.00 sec)
3. 配置slave备机（192.168.1.205），配置如下
原版本
[root@centos-cluster-s19423 soft]# cat /etc/my.cnf.bak2019513 @b@# For advice on how to change settings please see@b@# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html@b@@b@[mysqld]@b@#@b@# Remove leading # and set to the amount of RAM for the most important data@b@# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.@b@# innodb_buffer_pool_size = 128M@b@#@b@# Remove leading # to turn on a very important data integrity option: logging@b@# changes to the binary log between backups.@b@# log_bin@b@#@b@# Remove leading # to set options mainly useful for reporting servers.@b@# The server defaults are faster for transactions and fast SELECTs.@b@# Adjust sizes as needed, experiment to find the optimal values.@b@# join_buffer_size = 128M@b@# sort_buffer_size = 2M@b@# read_rnd_buffer_size = 2M@b@datadir=/var/lib/mysql@b@socket=/var/lib/mysql/mysql.sock@b@@b@# Disabling symbolic-links is recommended to prevent assorted security risks@b@symbolic-links=0@b@@b@log-error=/var/log/mysqld.log@b@pid-file=/var/run/mysqld/mysqld.pid@b@@b@@b@@b@@b@skip-grant-tables
修改后如下（改完重启mysql）
[root@centos-cluster-s19423 ~]# cat /etc/my.cnf@b@# For advice on how to change settings please see@b@# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html@b@@b@[mysqld]@b@#@b@# Remove leading # and set to the amount of RAM for the most important data@b@# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.@b@# innodb_buffer_pool_size = 128M@b@#@b@# Remove leading # to turn on a very important data integrity option: logging@b@# changes to the binary log between backups.@b@# log_bin@b@#@b@# Remove leading # to set options mainly useful for reporting servers.@b@# The server defaults are faster for transactions and fast SELECTs.@b@# Adjust sizes as needed, experiment to find the optimal values.@b@# join_buffer_size = 128M@b@# sort_buffer_size = 2M@b@# read_rnd_buffer_size = 2M@b@datadir=/var/lib/mysql@b@socket=/var/lib/mysql/mysql.sock@b@@b@log-bin=mysql-bin@b@@b@binlog_format=mixed@b@@b@server-id=2@b@#relay_log=mysql-relay-bin@b@log_slave_updates=1@b@@b@#replicate-do-db：需要同步的数据库名称，与master上的配置保持一致。@b@replicate-do-db=mytest@b@# Disabling symbolic-links is recommended to prevent assorted security risks@b@symbolic-links=0@b@@b@log-error=/var/log/mysqld.log@b@pid-file=/var/run/mysqld/mysqld.pid @b@@b@skip-grant-tables@b@skip-name-resolve@b@@b@log_timestamps=SYSTEM@b@@b@#跳过1032异常错误@b@slave-skip-errors=1032@b@@b@[root@centos-cluster-s19423 ~]# service mysqld restart
同时登录mysql进行配置(change master to部分根据上面master状态进行同步配置)
[root@centos-cluster-s19423 ~]# mysql -uroot -p123456;@b@mysql>change master to master_host='192.168.1.204', master_user='root', master_password='123456',master_log_file='mysql-bin.000007', master_log_pos=1581;@b@mysql> stop slave;@b@Query OK, 0 rows affected (0.00 sec)@b@@b@mysql> set global sql_slave_skip_counter=1;@b@Query OK, 0 rows affected (0.00 sec)@b@@b@mysql> stop slave; @b@Query OK, 0 rows affected (0.02 sec)@b@@b@mysql> show slave status;@b@+----------------------------------+---------------+-------------+-------------+---------------+------------------+---------------------+----------------------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+--------------------------------------+----------------------------+-----------+---------------------+--------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+@b@| Slave_IO_State | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error | Replicate_Ignore_Server_Ids | Master_Server_Id | Master_UUID | Master_Info_File | SQL_Delay | SQL_Remaining_Delay | Slave_SQL_Running_State | Master_Retry_Count | Master_Bind | Last_IO_Error_Timestamp | Last_SQL_Error_Timestamp | Master_SSL_Crl | Master_SSL_Crlpath | Retrieved_Gtid_Set | Executed_Gtid_Set | Auto_Position | Replicate_Rewrite_DB | Channel_Name | Master_TLS_Version |@b@+----------------------------------+---------------+-------------+-------------+---------------+------------------+---------------------+----------------------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+--------------------------------------+----------------------------+-----------+---------------------+--------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+@b@| Waiting for master to send event | 192.168.1.204 | root | 3306 | 60 | mysql-bin.000007 | 1581 | centos-cluster-s19423-relay-bin.000031 | 1183 | mysql-bin.000007 | Yes | Yes | mytest | | | | | | 0 | | 0 | 1581 | 1572 | None | | 0 | No | | | | | | 0 | No | 0 | | 0 | | | 1 | b1bfa5f4-6a95-11e9-8049-525400534aff | /var/lib/mysql/master.info | 0 | NULL | Slave has read all relay log; waiting for more updates | 86400 | | | | | | | | 0 | | | |@b@+----------------------------------+---------------+-------------+-------------+---------------+------------------+---------------------+----------------------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+--------------------------------------+----------------------------+-----------+---------------------+--------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+@b@1 row in set (0.00 sec)@b@@b@ @b@@b@@b@@b@mysql> start slave;@b@Query OK, 0 rows affected (0.00 sec)
4. 通过navicat连接204主机在mytest数据库的tb_test表插入数据后，连接205备机立马同步查询得到