Shulou(Shulou.com)06/02 Report--

Master-slave replication consists of two steps: settings on the master master server (group) and settings on the slave slave server (group).

Configure the primary server master

1. If it is not enabled, you need to open the binary log.

Set a unique server_id to the master, and all slave slave servers should also set the server_id; server_ id value to be an integer number (1 ~ 2 ^ 31-1). The server_id of each server in the same replication group (replicating group) must be unique.

[mysqld]

Server-id=1

Log-bin=mysql-bin

Binlog_format=mixed

2. Create a copy account and grant replication slave permission. The slave secondary server requires permission to connect and copy from the master. Typically, a separate user (user) is created for each slave, and only replication permissions (REPLICATION SLAVE permissions) are granted.

Example

GRANT REPLICATION SLAVE ON *. * TO 'replication_user'@'slave_host' IDENTIFIED BY' bigs3cret'

FLUSH PRIVILEGES

MariaDB [(none)] > grant replication slave on *. * to 'repl_user'@'192.168.1.53' identified by' pancou'

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)] > flush privileges

Query OK, 0 rows affected (0.00 sec)

It is important to note that there are some system configuration options that may affect master-slave replication. Check the following variables to avoid problems:

Skip-networking, if "skip-networking=1", the server will be restricted to localhost connections, preventing other machines from connecting remotely to this server.

Bind_address, similarly, if the server listens for only 127.0.0.1 (localhost) TCP/IP connections, the remote slave cannot connect to the server.

3. On the main database, the read lock is set to be valid. This operation is to ensure that there are no database operations in order to obtain a consistent snapshot:

MariaDB [(none)] > flush tables with read lock

Query OK, 0 rows affected (0.00 sec)

4. Then get the current binary file name and offset value on the main library. The purpose of this operation is to start data recovery from this point after the database is started.

MariaDB [(none)] > show master status

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | |

+-+

| | mysql-bin.000002 | 509 |

+-+

1 row in set (0.00 sec)

5. Now that the update operation of the master data has been stopped, a backup of the master database is needed. If the primary database can be stopped, then copying the data files directly should be the fastest way.

[root@localhost] # mysqldump-uroot-p-quick-- all-databases-- lock-all-tables-- master-data=2 > / opt/data-all.sql

Enter password:

[root@localhost] # ll-h / opt/data-all.sql

-rw-r--r-- 1 root root 3.7m May 2 15:08 / opt/data-all.sql

6. After the master database is backed up, you can resume the write operation, and the rest of the operation only needs to be done on the slave library.

MariaDB [(none)] > unlock tables

Query OK, 0 rows affected (0.00 sec)

7. Transfer the consistent backup data of the master database to the slave database.

[root@localhost] # rsync-avH-- progress'- e ssh-p 22' / opt/data-all.sql root@192.168.1.53:/tmp/

The authenticity of host '192.168.1.53 (192.168.1.53)' can't be established.

RSA key fingerprint is 75:b3:14:47:e1:73:10:24:a8:8f:b8:05:29:3e:7d:30.

Are you sure you want to continue connecting (yes/no)? Yes

Warning: Permanently added '192.168.1.53' (RSA) to the list of known hosts.

Reverse mapping checking getaddrinfo for bogon [192.168.1.53] failed-POSSIBLE BREAK-IN ATTEMPT!

Root@192.168.1.53's password:

Sending incremental file list

Data-all.sql

3863888 23.88MB/s 0:00:00 (xfer#1, to-check=0/1)

Sent 3864436 bytes received 31 bytes 594533.38 bytes/sec

Total size is 3863888 speedup is 1.00

Configure the secondary server slave

1. Assign a unique server_id to slave. All servers, whether master or slave, should be set up with server_id. The server_ id value can be an integer number (1 ~ 2 ^ 31-1), and the server_id of each server (/ server) in the same replication group (replicating group) must be unique.

For this configuration item to take effect, you need to restart the service.

[mysqld]

Server-id=2

2. Recover data from the library

[root@localhost ~] # mysql change master to master_host='192.168.1.78',master_user='repl_user',master_password='pancou',master_log_file='mysql-bin.000002',master_log_pos=509

Query OK, 0 rows affected (0.79 sec)

5. Start the slave process on the slave library

MariaDB [(none)] > start slave

Query OK, 0 rows affected (0.00 sec)

6. On salve, executing the show processlist command will display a process similar to the following:

MariaDB [(none)] > show processlist\ G

* * 1. Row *

Id: 5

User: root

Host: localhost

Db: NULL

Command: Query

Time: 0

State: init

Info: show processlist

Progress: 0.000

* 2. Row * *

Id: 8

User: system user

Host:

Db: NULL

Command: Connect

Time: 10

State: Waiting for master to send event

Info: NULL

Progress: 0.000

* 3. Row * *

Id: 9

User: system user

Host:

Db: NULL

Command: Connect

Time: 4

State: Slave has read all relay log; waiting for the slave I/O thread to update it

Info: NULL

Progress: 0.000

3 rows in set (0.00 sec)

This indicates that slave is connected to master and starts to receive logs.

Processes on the main library:

MariaDB [(none)] > show processlist\ G

* * 1. Row *

Id: 7

User: root

Host: localhost

Db: NULL

Command: Query

Time: 0

State: init

Info: show processlist

Progress: 0.000

* 2. Row * *

Id: 9

User: repl_user

Host: 192.168.1.53:57532

Db: NULL

Command: Binlog Dump

Time: 183

State: Master has sent all binlog to slave; waiting for binlog to be updated

Info: NULL

Progress: 0.000

2 rows in set (0.00 sec)

7. View the copy status from the library

MariaDB [(none)] > show slave status\ G

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.1.78

Master_User: repl_user

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000003

Read_Master_Log_Pos: 479

Relay_Log_File: localhost-relay-bin.000004

Relay_Log_Pos: 767

Relay_Master_Log_File: mysql-bin.000003

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 479

Relay_Log_Space: 1112

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 1

Master_SSL_Crl:

Master_SSL_Crlpath:

Using_Gtid: No

Gtid_IO_Pos:

Replicate_Do_Domain_Ids:

Replicate_Ignore_Domain_Ids:

Parallel_Mode: conservative

1 row in set (0.00 sec)

Verify the correctness of the replication service and perform an update operation on the master database to see if it is synchronized on the slave database.

MariaDB [(none)] > create database pancou

Query OK, 1 row affected (0.00 sec)

MariaDB [(none)] > use database pancou

ERROR 1049 (42000): Unknown database' database'

MariaDB [(none)] > use pancou

Database changed

MariaDB [pancou] > create table rpel_table (id int (3))

Query OK, 0 rows affected (0.39 sec)

MariaDB [pancou] > insert rpel_table value (1), (2), (3), (4), (5)

Query OK, 5 rows affected (0.04 sec)

Records: 5 Duplicates: 0 Warnings: 0

MariaDB [pancou] > select * from rpel_table

+-+

| | id |

+-+

| | 1 |

| | 2 |

| | 3 |

| | 4 |

| | 5 |

+-+

5 rows in set (0.00 sec)

MariaDB [pancou] > show master status

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | |

+-+

| | mysql-bin.000003 | 913 | |

+-+

1 row in set (0.00 sec)

View the slave library:

MariaDB [(none)] > select * from pancou.rpel_table

+-+

| | id |

+-+

| | 1 |

| | 2 |

| | 3 |

| | 4 |

| | 5 |

+-+

5 rows in set (0.00 sec)

MariaDB [(none)] > show slave status\ G

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.1.78

Master_User: repl_user

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000003

Read_Master_Log_Pos: 913

Relay_Log_File: localhost-relay-bin.000004

Relay_Log_Pos: 1201

Relay_Master_Log_File: mysql-bin.000003

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 913

Relay_Log_Space: 1546

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 1

Master_SSL_Crl:

Master_SSL_Crlpath:

Using_Gtid: No

Gtid_IO_Pos:

Replicate_Do_Domain_Ids:

Replicate_Ignore_Domain_Ids:

Parallel_Mode: conservative

1 row in set (0.00 sec)

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.