企业生产MySQL主从同步配置

前言：测试环境

一台mysql多个实例

主机IP地址 10.0.0.52

Master   3306

Salve    3307

一、主库要开启binlog服务

1. 1修改配置文件3306/my.cnf

[root@db02 ~]# egrep "log-bin|server-id" /data/3306/my.cnf  
log-bin = /data/3306/mysql-bin
server-id = 1

1. 2查看主库有没有开启binlog服务

方法一：

 mysql -uroot -pylh123 -S /data/3306/mysql.sock -e "show variables like ‘log_bin‘";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |   <=====ON表示开启
+---------------+-------+

方法二：

查看3306实例目录下面有没有日志文件

[root@db02 ~]# ll /data/3306/mysql-*
-rw-rw---- 1 mysql mysql 7680 Jan  4 14:37 /data/3306/mysql-bin.000001
-rw-rw---- 1 mysql mysql  221 Jan  4 14:37 /data/3306/mysql-bin.000002
-rw-rw---- 1 mysql mysql  221 Jan  4 14:37 /data/3306/mysql-bin.000003
-rw-rw---- 1 mysql mysql  221 Jan  4 14:37 /data/3306/mysql-bin.000004
-rw-rw---- 1 mysql mysql  221 Jan  4 14:37 /data/3306/mysql-bin.000005
... ...
-rw-rw---- 1 mysql mysql  126 Jan  4 21:18 /data/3306/mysql-bin.000029
-rw-rw---- 1 mysql mysql  107 Jan  4 21:18 /data/3306/mysql-bin.000030
-rw-rw---- 1 mysql mysql  140 Dec 16 09:46 /data/3306/mysql-bin.index

二、从库关闭binlog服务

修改配置文件3307/my.cnf

[root@db02 ~]# egrep "log-bin|server-id" /data/3307/my.cnf  
#log-bin = /data/3307/mysql-bin
server-id = 3

####如果做级联要打开binlog服务，还有其他配置。

三，添加一个账号允许从库可以同步

1、建立用于从库复制的账号rep

mysql> grant replication slave on *.* to ‘rep‘@‘10.0.0.%‘ identified by ‘ylh123‘;
Query OK, 0 rows affected (0.02 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.02 sec) #<====刷新到磁盘中去

# replication slave 为mysql同步的必须权限，此次不要授权all

# *.* 表示所有库所有表，也可以指定具体的库和表进行复制。oldboy.test。

# ‘rep‘@‘10.0.0.%‘ rep为同步账号。10.0.0.%位授权主机网段，使用了%表示允许整个10.0.0.0网段以rep用户访问。

2、查看rep用户有哪些权限

mysql> show grants for ‘rep‘@‘10.0.0.%‘;
+------------------------------------------------------------------------------------+
| Grants for rep@10.0.0.%                                                                                               |
+------------------------------------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO ‘rep‘@‘10.0.0.%‘ IDENTIFIED BY PASSWORD ‘*FE28814B4A8B3309DAC6ED7D3237ADED6DA1E515‘ |
+------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>

四、在主库做备份

4. 1 在主库中添加读锁

mysql> flush table with read lock;
Query OK, 0 rows affected (0.30 sec)
##不让别的用户往里面写

4. 2 开始备份数据库

4.2.1首先查看一下主库的状态

mysql> flush table with read lock;
Query OK, 0 rows affected (0.30 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000030 |      107 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

#前面添加了只读锁，107是备份点，从这个点开始进行备份

mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |      7680 |
| mysql-bin.000002 |       221 |
| mysql-bin.000003 |       221 |
| mysql-bin.000004 |       221 |
| mysql-bin.000005 |       221 |
... ...
| mysql-bin.000029 |       126 |
| mysql-bin.000030 |       107 |
+------------------+-----------+
30 rows in set (0.00 sec)

4.2.2 开始备份

方法一：

[root@db02 ~]# mysqldump -uroot -pylh123 -S /data/3306/mysql.sock -A -B --events|gzip >/opt/rep.sql.gz

方法二：

[root@db02 ~]# mysqldump -uroot -pylh123 -S /data/3306/mysql.sock -A -B --events --master-data=2 >/opt/rep.sql

##查看一下是否是从107点备份

[root@db02 ~]# grep "mysql-bin.000030" /opt/rep.sql      
-- CHANGE MASTER TO MASTER_LOG_FILE=‘mysql-bin.000030‘, MASTER_LOG_POS=107;

4.2.3再查看一下数据库有没有锁定2663备份点有没有变化

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000030 |       107 |             |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

4.2.4 解出读锁

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

五、将主库的数据导入到从库

提示：该环境是一台MySQL数据库多个实例，所以如果想测试两要服务器只需要将rep.sql数据推送到从数据库即可

mysql -uroot -pylh456 -S /data/3307/mysql.sock </opt/rep.sql

登录到3307从库中

mysql -uroot -p456 -S /data/3307/mysql.sock
mysql> CHANGE MASTER TO
    -> MASTER_HOST=‘10.0.0.52‘,
    -> MASTER_PORT=3306,
    -> MASTER_USER=‘rep‘,
    -> MASTER_PASSWORD=‘ylh123‘,
    -> MASTER_LOG_FILE=‘mysql-bin.000030‘,
    -> MASTER_LOG_POS=107;
Query OK, 0 rows affected (0.04 sec)

###上面填写的主库信息都放在3307的master.info文件中

[root@db02 ~]# cat  /data/3307/data/master.info
18
mysql-bin.000005
2663
10.0.0.52
rep
oldboy123
3306
60
0
0
1800.000
0

#当主库放binlog日志的时候从库的master.info文件会不断的更新里面的信息

MASTER_LOG_FILE=‘mysql-bin.000030‘,
MASTER_LOG_POS=107;

六、开启slave从库开关

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

##查看从库是否开启进程IO、SQL

mysql> show slave status\G

*************************** 1. row ***************************

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 10.0.0.52

                  Master_User: rep

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000030

          Read_Master_Log_Pos: 107

               Relay_Log_File: relay-bin.000003

                Relay_Log_Pos: 253

        Relay_Master_Log_File: mysql-bin.000030

             Slave_IO_Running: Yes

            Slave_SQL_Running: Yes

              Replicate_Do_DB: 

          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

              Relay_Log_Space: 403

              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

1 row in set (0.01 sec)

七、测试结果是否同步

#1.主库创建oldboy库

mysql -uroot -pylh123 -S /data/3306/mysql.sock
create database ylh;

#2.登录从库查看结果

mysql -uroot -pylh123 -S /data/3307/mysql.sock
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| ylh                |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)

提示：该环境是一台MySQL服务器多个实例，操作全部都在一台MySQL服务器上也就是主机db02

如何配置MySQL多实例部署方案，敬请关注我的博客http://yulianhui.blog.51cto.com/

本文出自 “Linux高级运维之路” 博客，请务必保留此出处http://yulianhui.blog.51cto.com/10829691/1731537

企业生产MySQL主从同步配置

标签：mysql主从同步企业实战案例