共計 4484 個字符，預計需要花費 12 分鐘才能閱讀完成。
怎樣簡單解析 mysql 主從復制，很多新手對此不是很清楚，為了幫助大家解決這個難題，下面丸趣 TV 小編將為大家詳細講解，有這方面需求的人可以來學習下，希望你能有所收獲。
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)
4.show file and position
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 | | | |
+——————+———-+————–+——————+——————-+
5.cold backup
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)
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;
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)
10.startup slave process
start slave;
mysql start slave;
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)
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)
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;
select * from repl_test;
+——+
| id |
+——+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
+——+
9 rows in set (0.00 sec)
看完上述內容是否對您有幫助呢？如果還想對相關知識有進一步的了解或閱讀更多相關文章，請關注丸趣 TV 行業資訊頻道，感謝您對丸趣 TV 的支持。
