mysql主从复制（异步复制）

最新推荐文章于 2024-03-18 06:00:00 发布

P是shi的叹息

最新推荐文章于 2024-03-18 06:00:00 发布

阅读量1.2k

点赞数

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.csdn.net/sinat_37073641/article/details/53737830

版权

6 篇文章 0 订阅

订阅专栏

  master ：192.168.1.122 

  slave：192.168.1.121 

  异步复制： 

  首先确认主库和从库是否一致，最好都是刚刚初始化的干净的数据库 

  如果主库正在使用不能初始化，就要先将主库的内容复制到从库上再做同步 

  先把master做一个备份，把备份出的文件传给slave 

  [root@linux3 mysql]# mysqldump -uroot -B test3 > test3.sql 

  [root@linux3 mysql]# scp test3.sql root@192.168.1.121:/mysql 

  再到slave上将数据恢复 

  mysql> source /mysql/test3.sql 

  mysql> show databases; 

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

  | Database           | 

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

  | information_schema | 

  | mysql              | 

  | performance_schema | 

  | test1              | 

  | test2              | 

  | test3              | 

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

  6 rows in set (0.00 sec) 

  恢复完成回到master继续配置 

  赋予用户权限 

  mysql> grant replication slave on *.* to 'repl'@'%' identified by 'oracle'; 

  mysql> flush privileges; 

  mysql> show master status;       
 要记住position 

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

  | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB         | Executed_Gtid_Set | 

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

  | mysql-bin.000014 |      407 |              | mysql,information_schema |                   | 

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

  1 row in set (0.00 sec) 

  再到slave 

  mysql> change master to master_host='192.168.1.122',master_port=3306,master_user='repl', 

      -> master_password='oracle',master_log_file='mysql-bin.000014',master_log_pos=407; 

  Query OK, 0 rows affected, 2 warnings (0.03 sec) 

  mysql> start slave; 

  Query OK, 0 rows affected (0.02 sec) 

  mysql> show slave status\G; 

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

  Slave_IO_State: Waiting for master to send event 

  Master_Host: 192.168.1.122 

  Master_User: repl 

  Master_Port: 3306 

  Connect_Retry: 60 

  Master_Log_File: mysql-bin.000014 

  Read_Master_Log_Pos: 407 

  Relay_Log_File: my3306-relay-bin.000002 

  Relay_Log_Pos: 283 

  Relay_Master_Log_File: mysql-bin.000014 

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

  Relay_Log_Space: 457 

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

  Master_UUID: 74980ce5-bbc2-11e6-9b35-000c29e93339 

  Master_Info_File: /mysql/data/master.info 

  SQL_Delay: 0 

  SQL_Remaining_Delay: NULL 

  Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it 

  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 

  1 row in set (0.00 sec) 

  看到两个yes 就基本成功了 可以在主库上进行一些操作来测试一下 

关注

专栏目录