mysql主从复制(冷备)
1.create repl user on master
grant replication slave on *.* to 'repl'@'10.124.110.116' identified by 'doudou123';
- root@(none) 10:13>grant replication slave on *.* to 'repl'@'10.124.110.116' identified by 'doudou123';
- 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
- root@(none) 10:35>show variables like '%log_bin%';
- +---------------------------------+-------------------------------+
- | Variable_name | Value |
- +---------------------------------+-------------------------------+
- | log_bin | ON |
- | log_bin_basename | /mysql/data/3307/mysql-bin.log| <==success
- | log_bin_index | /mysql/data/3307/binlog.index |
- | log_bin_trust_function_creators | ON |
- | log_bin_use_v1_row_events | OFF |
- | sql_log_bin | ON |
- +---------------------------------+-------------------------------+
-
- root@(none) 10:36>show variables like '%server_id%';
- +----------------+-------+
- | Variable_name | Value |
- +----------------+-------+
- | server_id | 113 | <==success
- | server_id_bits | 32 |
- +----------------+-------+
- 2 rows in set (0.00 sec)
3.flush tables
flush tables with read lock;
- root@(none) 10:42>flush tables with read lock;
- Query OK, 0 rows affected (0.00 sec)
show master status;
- root@(none) 14:57>show master status;
- +------------------+----------+--------------+------------------+-------------------+
- | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
- +------------------+----------+--------------+------------------+-------------------+
- | mysql-bin.000008 | 120 | | | |
- +------------------+----------+--------------+------------------+-------------------+
tar -cvf 3307data.tar 3307
6.unlock tables on master
unlock tables;
- root@(none) 14:18>unlock tables;
- Query OK, 0 rows affected (0.02 sec)
[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;
- mysql> change master to
- -> master_host='10.124.110.113',
- -> master_port=3307,
- -> master_user='repl',
- -> master_password='doudou123',
- -> master_log_file='binlog.000008',
- -> master_log_pos=120;
- Query OK, 0 rows affected, 2 warnings (0.05 sec)
start slave;
- mysql> start slave;
- Query OK, 0 rows affected (0.01 sec)
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)
- this is all right !!!!
- mysql> show processlist \G
- *************************** 1. row ***************************
- Id: 1
- User: system user
- Host:
- db: NULL
- Command: Connect
- Time: 31769
- State: Slave has read all relay log; waiting for the slave I/O thread to update it
- Info: NULL
- *************************** 2. row ***************************
- Id: 2
- User: system user
- Host:
- db: NULL
- Command: Connect
- Time: 946
- State: Waiting for master to send event
- Info: NULL
- *************************** 3. row ***************************
- Id: 3
- User: event_scheduler
- Host: localhost
- db: NULL
- Command: Daemon
- Time: 946
- State: Waiting on empty queue
- Info: NULL
- *************************** 4. row ***************************
- Id: 5
- User: root
- Host: localhost
- db: test
- Command: Query
- Time: 0
- State: init
- Info: show processlist
- 4 rows in set (0.00 sec)
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;
- select * from repl_test;
-
- +------+
- | id |
- +------+
- | 1 |
- | 2 |
- | 3 |
- | 4 |
- | 5 |
- | 6 |
- | 7 |
- | 8 |
- | 9 |
- +------+
- 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/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26442936/viewspace-2123509/