ITPub博客

首页 > 数据库 > MySQL > mysql主从复制(冷备)

mysql主从复制(冷备)

原创 MySQL 作者:lovehewenyu 时间:2016-08-15 17:14:04 0 删除 编辑

mysql主从复制(冷备)


1.create repl user on master
grant replication slave on *.* to 'repl'@'10.124.110.116' identified by 'doudou123';

  1. root@(none) 10:13>grant replication slave on *.* to 'repl'@'10.124.110.116' identified by 'doudou123';
  2. Query OK, 0 rows affected (0.04 sec)

2.enable BINLOG and set server-id in my.conf and restart server
[mysqld]
log-bin = /mysql/data/3307/mysql-bin.log
server-id = 113

  1. root@(none) 10:35>show variables like '%log_bin%';
  2. +---------------------------------+-------------------------------+
  3. | Variable_name | Value |
  4. +---------------------------------+-------------------------------+
  5. | log_bin | ON |
  6. | log_bin_basename | /mysql/data/3307/mysql-bin.log| <==success
  7. | log_bin_index | /mysql/data/3307/binlog.index |
  8. | log_bin_trust_function_creators | ON |
  9. | log_bin_use_v1_row_events | OFF |
  10. | sql_log_bin | ON |
  11. +---------------------------------+-------------------------------+

  12. root@(none) 10:36>show variables like '%server_id%';
  13. +----------------+-------+
  14. | Variable_name | Value |
  15. +----------------+-------+
  16. | server_id | 113 | <==success
  17. | server_id_bits | 32 |
  18. +----------------+-------+
  19. 2 rows in set (0.00 sec)

3.flush tables
flush tables with read lock;

  1. root@(none) 10:42>flush tables with read lock;
  2. Query OK, 0 rows affected (0.00 sec)
4.show file and position
show master status;


  1. root@(none) 14:57>show master status;
  2. +------------------+----------+--------------+------------------+-------------------+
  3. | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
  4. +------------------+----------+--------------+------------------+-------------------+
  5. | mysql-bin.000008 | 120 | | | |
  6. +------------------+----------+--------------+------------------+-------------------+
5.cold backup
tar -cvf 3307data.tar 3307


6.unlock tables on master
unlock tables;

  1. root@(none) 14:18>unlock tables;
  2. Query OK, 0 rows affected (0.02 sec)
7.set only server-id in my.conf on slave
[mysqld]
server-id = 114
vi auto.cnf
server_uuid=57735006-38f1-11e6-862c-005056beb65f


8.startup server with --skip-slave-start on slave
mysqld_multi start 3307

9.set user,ip,port,replication log and position on slave
change master to
master_host='10.124.110.113',
master_port=3307,
master_user='repl',
master_password='doudou123',
master_log_file='mysql-bin.000008',
master_log_pos=120;

  1. mysql> change master to
  2.     -> master_host='10.124.110.113',
  3.     -> master_port=3307,
  4.     -> master_user='repl',
  5.     -> master_password='doudou123',
  6.     -> master_log_file='binlog.000008',
  7.     -> master_log_pos=120;
  8. Query OK, 0 rows affected, 2 warnings (0.05 sec)
10.startup slave process
start slave;


  1. mysql> start slave;
  2. Query OK, 0 rows affected (0.01 sec)
11.show processlist on slave
show processlist \G


mysql> show processlist \G
*************************** 1. row ***************************
     Id: 1
   User: event_scheduler
   Host: localhost
     db: NULL
Command: Daemon
   Time: 10060
  State: Waiting on empty queue
   Info: NULL
*************************** 2. row ***************************
     Id: 2
   User: root
   Host: localhost
     db: NULL
Command: Query
   Time: 0
  State: init
   Info: show processlist
*************************** 3. row ***************************
     Id: 3
   User: system user
   Host:
     db: NULL
Command: Connect
   Time: 19
  State: Connecting to master   <=="Waiting for master to send event" is right. and some errors because password is wrong or server_uuid is the same.
## find errors using "show slave status \G"  and modify server_uuid using "auto.cnf" and find server_uuid using "show variables like '%server_uuid%';" ##
   Info: NULL
*************************** 4. row ***************************
     Id: 4
   User: system user
   Host:
     db: NULL
Command: Connect
   Time: 19
  State: Slave has read all relay log; waiting for the slave I/O thread to update it
   Info: NULL
4 rows in set (0.00 sec)


  1. this is all right !!!!
  2. mysql> show processlist \G
  3. *************************** 1. row ***************************
  4.      Id: 1
  5.    User: system user
  6.    Host:
  7.      db: NULL
  8. Command: Connect
  9.    Time: 31769
  10.   State: Slave has read all relay log; waiting for the slave I/O thread to update it
  11.    Info: NULL
  12. *************************** 2. row ***************************
  13.      Id: 2
  14.    User: system user
  15.    Host:
  16.      db: NULL
  17. Command: Connect
  18.    Time: 946
  19.   State: Waiting for master to send event
  20.    Info: NULL
  21. *************************** 3. row ***************************
  22.      Id: 3
  23.    User: event_scheduler
  24.    Host: localhost
  25.      db: NULL
  26. Command: Daemon
  27.    Time: 946
  28.   State: Waiting on empty queue
  29.    Info: NULL
  30. *************************** 4. row ***************************
  31.      Id: 5
  32.    User: root
  33.    Host: localhost
  34.      db: test
  35. Command: Query
  36.    Time: 0
  37.   State: init
  38.    Info: show processlist
  39. 4 rows in set (0.00 sec)
12.test change rows on master
use test
show tables;
create table repl_test(id int);
insert into repl_test values(1),(2),(3);
insert into repl_test values(4),(5),(6);
insert into repl_test values(7),(8),(9);


13.show test rows on slave
use test
show tables;

  1. select * from repl_test;

  2. +------+
  3. | id |
  4. +------+
  5. | 1 |
  6. | 2 |
  7. | 3 |
  8. | 4 |
  9. | 5 |
  10. | 6 |
  11. | 7 |
  12. | 8 |
  13. | 9 |
  14. +------+
  15. 9 rows in set (0.00 sec)

########################################################################################
版权所有,文章允许转载,但必须以链接方式注明源地址,否则追究法律责任!【QQ交流群:53993419】
QQ:14040928 E-mail:dbadoudou@163.com
本文链接: http://blog.itpub.net/blog/post/id/2123509/
########################################################################################

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26442936/viewspace-2123509/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
10年老鸟,Oracle,Mysql,EMC 存储,NBU备份

注册时间:2012-02-03

  • 博文量
    268
  • 访问量
    1299407