mysql主从、主主复制

目录

前提

  1. 数据库版本最好一致。（或slave库版本高于master库，不能相差太大）

master库修改

# 1、修改my.cnf
[mysqld]
log-bin=mysql-bin  # 打开二进制日志，8.0已自动开启
server-id=1 

# 2、重启mysql

# 3、创建replication账号密码
create user 'repl'@'%' identified with mysql_native_password by '123';  # 8.0 加密方式改变，需要如此
grant replication slave on *.* to  'repl'@'%';  # 赋予replication权限
flush privileges;  # 刷新

# 4、查看master状态，记录二进制文件名和位置
mysql > SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 73       | test         | manual,mysql     |
+------------------+----------+--------------+------------------+
## 默认传输所有表，如果只记录指定表
[mysqld]
# 不同步那些表
binlog-ignore-db = mysql  
binlog-ignore-db = test  
binlog-ignore-db = information_schema  
# 只同步那些表，除此之外，其他不同步
binlog-do-db = game

slave库修改

可以提前做连通性测试
mysql -urepl -h9.7.68.140 -p

# 1、修改my.cnf
[mysqld]
server-id=2  

# 2、重启mysql

# 3、执行同步SQL语句
mysql> change master to
    -> master_host='9.7.68.140',
    -> master_user='repl',
    -> master_password='123',
    -> master_log_file='mysql-bin.000001',
    -> master_log_pos=3173;

# 4、启动slave同步进程
start slave;

# 5、查看状态
show slave status\G

# 6.1、是否成功，以下都为yes即成功
slave_io_running: yes
slave_sql_running: yes

# 6.2 主库插入数据，从库查看

注：如果出现问题

  1. stop slave;
  2. reset slave;
  3. 重新配置
  4. start slave;
mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 9.7.68.140
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 4105
               Relay_Log_File: localhost-relay-bin.000002
                Relay_Log_Pos: 1256
        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: 4105
              Relay_Log_Space: 1469
              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: be46c74d-53ea-11eb-b094-005056bf6ee0
             Master_Info_File: mysql.slave_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: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_Namespace:

以上步骤完成 主从复制

下面为 主主复制

master库

# 1、修改my.cnf
[mysqld]
[mysqld1]
port=3306
basedir=/app/mysql
datadir=/app/mysql/data
socket=/app/mysql/data/mysqld.sock
log-error=/app/mysql/data/mysqld.error
# 主-从形式新加
log-bin=mysql-bin  # 开启二进制日志
server-id=1   # 设置server-id
# binlog-do-db = test
# binlog-ignore-db = mysql
# 主主形式另加
log-slave-updates
sync_binlog = 1
auto_increment_offset = 1
auto_increment_increment = 2
# replicate-do-db = test
# replicate-ignore-db = mysql,information_schema

# 2、重启

# 3、创建用户，授权
create user 'repl'@'%' identified with mysql_native_password by '123';  # 8.0 加密方式改变，需要如此
grant replication slave on *.* to  'repl'@'%';  # 赋予replication权限
flush privileges;  # 刷新

# 4、查看master状态
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |     1218 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

slave库

# 1、修改my.cnf
[mysqld]
port=3307
basedir=/app/mysql
datadir=/app/mysql/data1
socket=/app/mysql/data1/mysqld.sock
log-error=/app/mysql/data1/mysqld.err
# 主-从
server-id=2
log-bin=mysql-bin 
# 主-主 新加
log-slave-updates
sync_binlog = 1
auto_increment_offset = 2
auto_increment_increment = 2
# binlog-do-db = test
# binlog-ignore-db = mysql

# 2、 重启

# 3、创建用户，授权
create user 'repl'@'%' identified with mysql_native_password by '123';  # 8.0 加密方式改变，需要如此
grant replication slave on *.* to  'repl'@'%';  # 赋予replication权限
flush privileges;  # 刷新

# 4、查看master状态
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |     1209 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

同步

#  A端
1、 stop slave;
2、
mysql> change master to
    -> master_host='9.7.68.147',
    -> master_port=3307,
    -> master_user='repl_slave',
    -> master_password='123',
    -> master_log_file='mysql-bin.000001',
    -> master_log_pos=156;

3、start slave;
4、show slave status\G
# 2、 B端
1、stop slave;
2、
mysql> change master to 
    -> master_host='9.7.68.140',
    -> master_user='repl',
    -> master_password='123',
    -> master_log_file='mysql-bin.000002',
    -> master_log_pos=156;
3、stop slave;
4、show slave status\G


# 是否成功，以下都为yes即成功
slave_io_running: yes
slave_sql_running: yes

#  主库插入数据，从库查看

配置参数说明

  • server-id
    • ID值唯一标识了复制群集中的主从服务器
  • log-bin
    • 表示打开binlog，打开才可以通过IO写到slave的relay-log，也是可以进行replication的前提
  • binlog-do-db
    • 表示记录二进制日志的数据库。多个用 逗号 隔开
  • binlog-ignore-db
    • 表示不记录二进制日志的数据库。多个用 逗号 隔开
  • replication-do-db
    • 表示需要同步的数据库。多个用 逗号 隔开
  • replication-ignore-db
    • 表示不需要同步数据库。多个用 逗号 隔开
  • master-connect-retry
    • master-connect-retry=n 表示从服务器与主服务器的连接没有成功，则等待n秒（默认60s）。如果从服务器存在master.info文件，忽略此选项
  • log-slave-updates
    • 配置从库上的更新操作是否写入二进制文件。如果这台从库，还要做其他从库的主库，需要打开
  • slave-skip-errors
    • 复制过程中，binlog中的sql出错。默认从库停止复制。使用该参数定义错误号，如果遇见错误是定义的错误号，便跳过。可能会导致数据库不一致
  • sync_binlog=1 or N
    • 默认是0，这种模式，mysql不会同步到磁盘中。mysql依赖操作系统刷新二进制binlog。
    • N，表示每N次binlog写入后与硬盘同步
    • 1，最安全的，最多丢失一个语句
  • auto_increment_offset 和 auto_increment_increment
    • 用于主 - 主 复制，并用来控制auto_increment列的操作。
    • auto_increment_increment：控制列中的值是增量值
    • auto_increment_offset：确定auto_increment列值的起点
    • 如果auto_increment_offset大于auto_increment_increment值，则auto_increment_offset值被忽略。

posted on 2021-01-15 16:01  snail_z  阅读(122)  评论(0)    收藏  举报

导航