MySQL 主从复制 (多实例间)


:Mr.zhou  阅读: 1,951 次

  同一台服务器不同实例间的主从复制。

  一、创建复制账号

  二、配置主库和从库

  三、启动复制

  四、测试

  多实例配置:http://www.z-dig.com/mysql-multiple-instances.html

  演示环境为以上链接中的环境:

[root@mysql ~]# tree -L 2 /mysql
/mysql
├── 3306
│   ├── data
│   ├── my.cnf
│   ├── mysql
│   └── mysql.err
└── 3307
    ├── data
    ├── my.cnf
    ├── mysql
    └── mysql.err

4 directories, 6 files
[root@mysql ~]#

[root@mysql ~]# sh /mysql/3306/my
my.cnf     mysql      mysql.err  
[root@mysql ~]# sh /mysql/3306/mysql start
Starting MySQL ...
[root@mysql ~]# sh /mysql/3307/mysql start 
Starting MySQL ...
[root@mysql ~]# lsof -i :3306
COMMAND  PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld  1547 mysql   10u  IPv4  11352      0t0  TCP *:mysql (LISTEN)
[root@mysql ~]# lsof -i :3307
COMMAND  PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld  1710 mysql   10u  IPv4  11467      0t0  TCP *:opsession-prxy (LISTEN)
[root@mysql ~]# 

  实例1:端口 3306 ; 数据库存放目录:/mysql/3306/data/ ; 配置文件及脚本存放目录:/mysql/3306/

  实例2:端口 3307 ; 数据库存放目录:/mysql/3307/data/ ; 配置文件及脚本存放目录:/mysql/3307/

  实例1为主库,实例2为从库。

  一、创建复制账号

  在主库创建复制使用的账号,账号:repl 密码:123456

  在从库创建相同的复制账号,方便主从切换。(若使用 mysqldump 导出主库全部数据库 或 不进行特殊过滤配置,主库的账号信息(mysql库)会覆盖从库的账号信息(mysql库)。)

  复制账户需要的权限为:”REPLICATION SLAVE”

[root@mysql ~]# cd /mysql/3306
[root@mysql 3306]# pwd
/mysql/3306
[root@mysql 3306]# ls
data  my.cnf  mysql  mysql.err  mysql.pid  mysql.sock
[root@mysql 3306]# mysql -S ./mysql.sock -uroot -p123456 -e "select user,host from mysql.user;"
+-------+-----------------+
| user  | host            |
+-------+-----------------+
| root  | 127.0.0.1       |
| user1 | 172.16.1.%      |
| root  | ::1             |
|       | localhost       |
| root  | localhost       |
|       | mysql.z-dig.com |
| root  | mysql.z-dig.com |
+-------+-----------------+
[root@mysql 3306]# 


[root@mysql 3306]# mysql -S ./mysql.sock -uroot -p123456 -e "grant replication slave on *.* to repl@'localhost' identified by '123456';flush privileges;"
[root@mysql 3306]# 


[root@mysql 3306]# mysql -S ./mysql.sock -uroot -p123456 -e "select user,host from mysql.user;"                       
+-------+-----------------+
| user  | host            |
+-------+-----------------+
| root  | 127.0.0.1       |
| user1 | 172.16.1.%      |
| root  | ::1             |
|       | localhost       |
| repl  | localhost       |
| root  | localhost       |
|       | mysql.z-dig.com |
| root  | mysql.z-dig.com |
+-------+-----------------+
[root@mysql 3306]# 
[root@mysql 3306]# cd /mysql/3307/
[root@mysql 3307]# pwd
/mysql/3307
[root@mysql 3307]# ls
data  my.cnf  mysql  mysql.err  mysql.pid  mysql.sock
[root@mysql 3307]# mysql -S ./mysql.sock -uroot -p654321 -e "grant replication slave on *.* to repl@'localhost' identified by '123456';flush privileges;"      
[root@mysql 3307]# mysql -S ./mysql.sock -uroot -p654321 -e "select user,host from mysql.user;"                       +-------+-----------------+
| user  | host            |
+-------+-----------------+
| root  | 127.0.0.1       |
| user2 | 172.16.1.%      |
| root  | ::1             |
|       | localhost       |
| repl  | localhost       |
| root  | localhost       |
|       | mysql.z-dig.com |
| root  | mysql.z-dig.com |
+-------+-----------------+
[root@mysql 3307]#

  二、配置主库和从库

  修改各实例配置文件,为各实例分配独一无二的 server ID 。

  在主库开启二进制日志。设置二进制日志的存放路径及文件名。

  在从库开启二进制日志。设置二进制日志的存放路径及文件名、中继日志的存放路径及文件名,配置从库为只读模式。若需要从库也记录二进制日志,则需启用”log_slave_updates”。

   主库

[root@mysql 3307]# cd /mysql/3306/
[root@mysql 3306]# ls
data  my.cnf  mysql  mysql.err  mysql.pid  mysql.sock
[root@mysql 3306]# 

[root@mysql 3306]# cat my.cnf 
[client]
port            = 3306
socket          = /mysql/3306/mysql.sock
[mysqld]
port            = 3306
socket          = /mysql/3306/mysql.sock
basedir         = /usr/local/mysql
datadir         = /mysql/3306/data

# Replication Master
server_id       = 3306
log_bin         = /mysql/3306/mysql-bin
sync_binlog     = 1
innodb_flush_log_at_trx_commit = 1
expire_logs_days = 7

[mysqld_safe]
log_error       = /mysql/3306/mysql.err
pid_file        = /mysql/3306/mysql.pid
[root@mysql 3306]# 

  从库

[root@mysql 3306]# cd /mysql/3307/
[root@mysql 3307]# ls
data  my.cnf  mysql  mysql.err  mysql.pid  mysql.sock
[root@mysql 3307]# 

[root@mysql 3307]# cat my.cnf 
[client]
port            = 3307
socket          = /mysql/3307/mysql.sock
[mysqld]
port            = 3307
socket          = /mysql/3307/mysql.sock
basedir         = /usr/local/mysql
datadir         = /mysql/3307/data

# Replication Master
server_id       = 3307
log_bin         = /mysql/3307/mysql-bin
relay_log       = /mysql/3307/mysql-relay-bin
log_slave_updates = 1
read_only       = 1
innodb_flush_log_at_trx_commit = 2

[mysqld_safe]
log_error       = /mysql/3307/mysql.err
pid_file        = /mysql/3307/mysql.pid
[root@mysql 3307]# 

  重启数据库

[root@mysql ~]# cd /mysql/3306/
[root@mysql 3306]# mysqladmin -S mysql.sock -uroot -p123456 -s shutdown
[root@mysql 3306]#
[root@mysql 3306]# sh mysql start
Starting MySQL ...
[root@mysql 3306]# ls
data    mysql             mysql-bin.index  mysql.pid
my.cnf  mysql-bin.000001  mysql.err        mysql.sock
[root@mysql 3306]# 
[root@mysql 3306]# cd /mysql/3307/
[root@mysql 3307]# ls
data  my.cnf  mysql  mysql.err  mysql.pid  mysql.sock
[root@mysql 3307]# mysqladmin -S mysql.sock -uroot -p654321 -s shutdown
[root@mysql 3307]#
[root@mysql 3307]# sh mysql start
Starting MySQL ...
[root@mysql 3307]# ls
data    mysql             mysql-bin.index  mysql.pid
my.cnf  mysql-bin.000001  mysql.err        mysql.sock
[root@mysql 3307]# 

  MySQL 复制开启之前,需要主从两数据库内容保持一致,并知道主从数据库一致时,主库 binlog 的文件名和偏移地址。

  在启动复制之前,要确保从库的数据库内容和主库保持一致。若主库和从库都是新部署的数据库,则可忽略,若主库已运行一段时间,或新增从库。则需要将主库的数据通过 mysqldump 导出,并在从库导入,使主从数据库内容一致。在使用 msyqldump 导出数据库时,可以使用 –master-data 记录主库此时的 binlog 文件名和偏移地址。若 –master-data=1 则会在从库导入时自动配置binlog的相关信息,若 –master-data=2 则在从库导入时不会自动配置从库的binlog相关信息,但导出的sql语句中会以注释的形式,记录导出数据库时的binlog信息。也可以登陆主库 mysql ,锁表,通过 “show master status;” 查看此时的 binlog 信息。再导出数据库。(在mysql会话中使用锁表命令后,若退出该会话则锁表自动解除。),记录主库binglog信息后即可,解除表锁定。

  导出主库

[root@mysql 3307]# cd /mysql/3306/
[root@mysql 3306]# mysqldump -S mysql.sock -uroot -p123456 --events -A -B -x --master-data > /tmp/masterdb.sql

  导入从库

[root@mysql 3306]# cd /mysql/3307/
[root@mysql 3307]# mysql -S mysql.sock -uroot -p654321 < /tmp/masterdb.sql 
[root@mysql 3307]# 

  在从库配置主库的相关信息,因为导出主库的数据时使用了 –master-data 选项,所以不需要手动配置binlog信息。若没有使用该选项,或者 –master-data=2 则需要手动配置binlog信息。

[root@mysql 3307]# mysql -S mysql.sock -uroot -p654321 < < EOF
> CHANGE MASTER TO
> MASTER_HOST='localhost',
> MASTER_PORT=3306,
> MASTER_USER='repl',
> MASTER_PASSWORD='123456';
> EOF
[root@mysql 3307]# 

  三、启动复制

[root@mysql 3307]# mysql -S mysql.sock -uroot -p654321 -e "start slave;"
[root@mysql 3307]# 

  在从库查看复制情况

[root@mysql 3307]# mysql -S mysql.sock -uroot -p654321 -e "show slave status\G;"
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: localhost
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 107
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 253
        Relay_Master_Log_File: mysql-bin.000001
             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: 107
              Relay_Log_Space: 409
              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: 3306
[root@mysql 3307]#

  Slave_IO_Running: Yes Slave_SQL_Running: Yes IO进程和SQL进程状态都为YES,启动复制成功。

  四、测试

  分别查看主从库中的数据库。

[root@mysql 3307]# mysql -S /mysql/3306/mysql.sock -uroot -p123456 -e "show databases;"
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
[root@mysql 3307]# 
[root@mysql 3307]# mysql -S mysql.sock -uroot -p654321 -e "show databases;"     
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
[root@mysql 3307]# 

  在主库新建数据库 db1

[root@mysql 3307]# mysql -S /mysql/3306/mysql.sock -uroot -p123456 -e "create database db1;"
[root@mysql 3307]# mysql -S /mysql/3306/mysql.sock -uroot -p123456 -e "show databases;"     
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |
| mysql              |
| performance_schema |
| test               |
+--------------------+
[root@mysql 3307]# 

  看从库是否将主库新建的 db1 库复制了过来

[root@mysql 3307]# mysql -S mysql.sock -uroot -p654321 -e "show databases;"
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |
| mysql              |
| performance_schema |
| test               |
+--------------------+
[root@mysql 3307]# 

  从库中已有 db1 。多实例间主从复制成功。

  MySQL 主从复制有多种架构。根据需求选择合适的架构。若要在已有主从复制中新增从库,则需要修改新增从库的配置文件,配置唯一的 server ID 并使从库的数据跟主库在某一时间点保持一致,并记录此时间点,主库binlog信息。配置新增从库的主库信息。开启复制。


转载请注明原文链接:http://www.z-dig.com/mysql-master-slave-replication-multiple-instance.html



正文部分到此结束