首页

在centos6.7的linux环境下基于两台mysql5数据库服务实现主从cluster集群配置详细步骤整理

标签:mysql集群扩容,MYSQL主从,mysql读写分离,mysql双机热备     发布时间:2019-05-16   

一、前言

centos6.7的linux操作系统环境下通过部署两台mysql主机服务器(一台master主机、另一台slave备机)实现mysqlmysql57-community-release-el6-9.noarch.rpm(本地下载)安装参见其他文章页面)主从集群cluster的双机热备,详细配置步骤整理如下。

在centos6.7的linux环境下基于两台mysql5数据库服务实现主从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备机立马同步查询得到