mysql主从复制

一、基本环境

操作系统:Red Hat Enterprise Linux Server release 6.8

数据库：MySQL Community Server 5.7.21

主节点ip：192.168.223.128

从节点ip：192.168.223.129

二、安装mysql

2.1，官网下载MySQL

mysql-5.7.21-1.el6.x86_64.rpm-bundle.tar

2.2，解压安装

两个节点都安装：

tar -xvf mysql-5.7.21-1.el6.x86_64.rpm-bundle.tar

rpm -ivh mysql-community-common-5.7.21-1.el6.x86_64.rpm

rpm -ivh mysql-community-libs-5.7.21-1.el6.x86_64.rpm

rpm -ivh mysql-community-devel-5.7.21-1.el6.x86_64.rpm

rpm -ivh mysql-community-client-5.7.21-1.el6.x86_64.rpm

rpm -ivh mysql-community-server-5.7.21-1.el6.x86_64.rpm

2.3，设置密码

编辑配置文件加入skip-grant-tables参数

vi /etc/my.cnf

# For advice on how to change settings please see

# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[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.

#

# 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

skip-grant-tables

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

启动

service mysqld start

登陆修改密码

mysql>update mysql.user set authentication_string=password('123456') where user='root' and Host = 'localhost';

mysql>alter user 'root'@'localhost' identified by '123';

mysql>flush privileges;

配置完成，去掉skip-grant-tables重启数据库

三、配置主从复制

3.1，主节点192.168.223.128配置

3.1.1，修改配置文件：

[root@postgredb-master opt]# vi /etc/my.cnf

# For advice on how to change settings please see

# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[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=mysql-bin           #开启二进制日志

server-id=1                 #任意自然数n，只要保证两台MySQL主机不重复就可以了,开启二进制日志模式下该参数必须指定

auto_increment_increment=2  #步进值auto_imcrement。一般有n台主MySQL就填n

auto_increment_offset=1     #起始值。一般填第n台主MySQL。此时为第一台主MySQL

#binlog-ignore=mysql         #忽略mysql库

#replicate-do-db=aa          #要同步的数据库，默认所有库

#

# 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

#skip-grant-tables

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

拷贝主节点数据库到从节点数据库

3.1.2，配置复制用户

mysql>grant replication slave on *.*  to 'myrep'@'192.168.223.%' identified by 'myrep';

3.2，从库配置

3.2.1，修改配置文件

[root@postgredb-slave opt]# vi /etc/my.cnf

# For advice on how to change settings please see

# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[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=mysql-bin           #开启二进制日志

server-id=2                 #任意自然数n，只要保证两台MySQL主机不重复就可以了,开启二进制日志模式下该参数必须指定

auto_increment_increment=2  #步进值auto_imcrement。一般有n台主MySQL就填n

auto_increment_offset=1     #起始值。一般填第n台主MySQL。此时为第一台主MySQL

#

# 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

#skip-grant-tables

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

3.2.2，配置slave

查看主节点日志情况

mysql> show master status;

+------------------+----------+--------------+------------------+-------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+------------------+----------+--------------+------------------+-------------------+

| mysql-bin.000002 |     1758 |              |                  |                   |

+------------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

配置

msql>change master to 

       ->master_host='192.168.223.128',

        ->master_user='myrep',

       ->master_password='myrep',

       ->master_log_file='mysql-bin.000002',

       ->master_log_pos=1758;

启动

mysql>start slave;

查看状态

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.223.128

                  Master_User: myrep

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000002

          Read_Master_Log_Pos: 2210

               Relay_Log_File: postgredb-slave-relay-bin.000003

                Relay_Log_Pos: 772

        Relay_Master_Log_File: mysql-bin.000002

             Slave_IO_Running: Yes

            Slave_SQL_Running: Yes

              Replicate_Do_DB: 

          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: 2210

              Relay_Log_Space: 989

              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: 1

                  Master_UUID: 1aef950e-316d-11e8-af0c-6876160dfdd0

             Master_Info_File: /var/lib/mysql/master.info

                    SQL_Delay: 0

          SQL_Remaining_Delay: NULL

      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

           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

         Replicate_Rewrite_DB: 

                 Channel_Name: 

           Master_TLS_Version: 

1 row in set (0.00 sec)

四、数据验证

4.1，主库生成数据

mysql> use test;

Database changed

mysql> create table test1(id int,name varchar(20));

Query OK, 0 rows affected (0.03 sec)

mysql> insert into test1 values(1,'jack');

Query OK, 1 row affected (0.01 sec)

4.2，从库验证

mysql> use test;

Database changed

mysql> show tables;

+----------------+

| Tables_in_test |

+----------------+

| test           |

| test1          |

+----------------+

2 rows in set (0.00 sec)

mysql> select * from test1;

+------+------+

| id   | name |

+------+------+

|    1 | jack |

+------+------+

1 row in set (0.00 sec)

数据验证成功，至此配置完成。

喜欢的朋友可以扫描以下二维码进行关注，公众号将每天更新文章：