怎样简单解析mysql主从复制
发布时间：2022-01-12 13:05:06 所属栏目：MySql教程 来源：互联网
导读：怎样简单解析mysql主从复制，很多新手对此不是很清楚，为了帮助大家解决这个难题，下面小编将为大家详细讲解，有这方面需求的人可以来学习下，希望你能有所收获。 mysql主从复制（冷备） 1.create repl user on master grant replication slave on *.* to re
|
怎样简单解析mysql主从复制，很多新手对此不是很清楚，为了帮助大家解决这个难题，下面小编将为大家详细讲解，有这方面需求的人可以来学习下，希望你能有所收获。 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) 看完上述内容是否对您有帮助呢？ （编辑：武汉站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
