企业生产MySQL主从同步配置

32221sdad

前言：测试环境
一台mysql多个实例
主机IP地址 10.0.0.52
Master   3306
Salve    3307
一、主库要开启binlog服务
1. 1修改配置文件3306/my.cnf

1 2 3

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

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

方法一：

1 2 3 4 5 6

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

方法二：
查看3306实例目录下面有没有日志文件

1 2 3 4 5 6 7 8 9 10

[iyunv@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

1 2 3

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

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

三，添加一个账号允许从库可以同步
1、建立用于从库复制的账号rep

1 2 3 4

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用户有哪些权限

1 2 3 4 5 6 7 8

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 在主库中添加读锁

1 2 3

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

4. 2 开始备份数据库
4.2.1首先查看一下主库的状态

1 2 3 4 5 6 7 8 9

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是备份点，从这个点开始进行备份

1 2 3 4 5 6 7 8 9 10 11 12 13 14

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 开始备份
方法一：

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

方法二：

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

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

1 2	[iyunv@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备份点有没有变化

1 2 3 4 5 6 7

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 解出读锁

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

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

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

登录到3307从库中

1 2 3 4 5 6 7 8 9

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文件中

1 2 3 4 5 6 7 8 9 10 11 12 13

[iyunv@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文件会不断的更新里面的信息

1 2	MASTER_LOG_FILE='mysql-bin.000030', MASTER_LOG_POS=107;

六、开启slave从库开关

1 2	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库

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

#2.登录从库查看结果

1 2 3 4 5 6 7 8 9 10 11

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