Shulou(Shulou.com)05/31 Report--

How to simply analyze mysql master-slave copy, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain for you in detail, people with this need can come to learn, I hope you can gain something.

Mysql master-slave replication (cold backup)

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 | show variables like'% server_id%' |

+-+ +

| | Variable_name | Value |

+-+ +

| | server_id | 113 | 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 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)

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.