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

This article will explain in detail how to create a database and configure the master and slave in Mysql. The content of the article is of high quality, so the editor will share it with you for reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

1. Create an account on the primary server for replication.

Mysql > GRANT REPLICATION SLAVE ON *. * TO 'repl'@'192.168.101.3' IDENTIFILED BY' Mysqlrepl'

Mysql > flush privileges

2. Modify the parameters of master server

[root@localhost ~] # vi / usr/my.cnf

Modify the following

Server-id = 1

Log-bin=mysql-bin

3. The master server backs up the data and transmits it

Mysql > flush tables with read lock

Mysql > show master status

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | |

+-+

| | mysql-bin.00002 | 120 | |

+-+

1 row in set (0.00 sec)

[root@localhost ~] # mysqldump-uroot-p-- all-databases | gzip > export_t.sql.gz

[root@localhost ~] # scp export_t.sql.gz root@192.168.101.3:/tmp/

Mysql > unlock tables

4. Recover data from the server

[root@localhost bin] # gunzip export_t.sql.gz

[root@localhost bin] # mysql-uroot-p

Mysql > source / tmp/export_t.sql.gz

5. Modify slave server configuration

[root@localhost bin] # vi / usr/my.cnf

Add the following

Server-id = 2

Restart the server

[root@localhost ~] # service mysql restar

6. Set the master-slave configuration from the server

Mysql > CHANGE MASTER TO

-> MASTER_HOST='192.168.101.5'

-> MASTER_USER='repl'

-> MASTER_PASSWORD='Mysqlrepl'

-> MASTER_LOG_FILE='mysql-bin.00002'

-> MASTER_PORT=3306

-> MASTER_LOG_POS=120

MASTER_HOST refers to the IP address of the primary server

MASTER_USER refers to a replicated account

MASTER_PASSWORD refers to the password of the account.

MASTER_PORT refers to the primary server port

MASTER_LOG_FILE refers to bin-log 's files.

MASTER_LOG_POS refers to log file bits

7. Start the slave thread from the server

Mysql > start slave

Mysql > show processlist

+-+ -+

| | Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined |

+-+ -+

| | 1 | system user | | NULL | Connect | 714 | Slave has read all relay log; waiting for the slave thread to update it O thread to update it | NULL | 0 | 0 |

| | 2 | system user | | NULL | Connect | 714 | Waiting for master to send event | NULL | 0 | 0 |

| | 4 | root | localhost | NULL | Query | 0 | init | show processlist | 0 | 0 |

+-+ -+

3 rows in set (0.08 sec)

This has shown that the master and slave have been built successfully.

8. Testing

In the primary server

Mysql > use test

Database changed

Mysql > select * from aaa

+-+ +

| | a | b | |

+-+ +

| | 1 | 2 |

| | 1 | 2 |

| | 1 | 2 |

| | 2 | 3 |

| | 2 | 7 |

+-+ +

5 rows in set (0.06 sec)

From the server

Mysql > use test

Database changed

Mysql > select * from aaa

+-+ +

| | a | b | |

+-+ +

| | 1 | 2 |

| | 1 | 2 |

| | 1 | 2 |

| | 2 | 3 |

| | 2 | 7 |

+-+ +

5 rows in set (0.21 sec)

In the primary server

Mysql > insert into aaa values (5, 7), (56, 21)

Query OK, 2 rows affected (0.21 sec)

Records: 2 Duplicates: 0 Warnings: 0

Mysql > select * from aaa

+-+ +

| | a | b | |

+-+ +

| | 1 | 2 |

| | 1 | 2 |

| | 1 | 2 |

| | 2 | 3 |

| | 2 | 7 |

| | 5 | 7 |

| | 56 | 21 |

+-+ +

7 rows in set (0.00 sec)

From the server

Mysql > select * from aaa

+-+ +

| | a | b | |

+-+ +

| | 1 | 2 |

| | 1 | 2 |

| | 1 | 2 |

| | 2 | 3 |

| | 2 | 7 |

| | 5 | 7 |

| | 56 | 21 |

+-+ +

7 rows in set (0.00 sec)

9. Management master and subordinate

View slave server status

Mysql > show slave status\ G

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.101.5

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.00002

Read_Master_Log_Pos: 120

Relay_Log_File: localhost-relay-bin.000001

Relay_Log_Pos: 283

Relay_Master_Log_File: mysql-bin.000036

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: 120

Relay_Log_Space: 460

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_UUID: 86d34969-fa5d-11e6-b372-000c29c88c3f

Master_Info_File: / usr/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

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

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position: 0

1 row in set (0.00 sec)

The two messages of concern are whether Slave_IO_Running and Slave_SQL_Running are YES. Slave_IO_Running reads the BINLOG log from the master server and writes it to the relay log of the slave server; Slave_SQL_Running is responsible for reading and executing the relay log information.

Note: if you need to disable writes from the server, you need to change the parameter read-only so that only data can be read from the server's non-root account.

On how to create a database in Mysql and configure the master and slave to share here, I hope the above content can be of some help to you, you can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.