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

Upfront preparation: two servers with Mysql installed, or two Mysql instances deployed on one server.

To avoid unnecessary errors, it is best to keep the Mysql version consistent.

+-+ +

| | Server address | Host name | Database version | Database port | server_id | role |

+-+ +

| | 192.168.175.248 | Mysql-248 | Mysql-5.6.30 | 3306 | 1 | main library Master |

+-+ +

| | 192.168.175.249 | Mysql-249 | Mysql-5.6.30 | 3306 | 2 | slave Slave |

+-+ +

1. Configuration of the main library:

1. Enable binary log and configure server_id (restart is required to take effect).

[root@Mysql-248 mysql-5.6.30] # grep-A3 'mysqld' my.cnf

[mysqld]

Port = 3306

Server_id = 1

Log-bin=mysql-bin

Verify the status of the binary log. ON is open:

Mysql > show variables like 'log_bin'

+-+ +

| | Variable_name | Value |

+-+ +

| | log_bin | ON |

+-+ +

1 row in set (0.00 sec)

two。 Establish a Mysql replication user in the main library.

Mysql > grant replication slave on *. * to 'repl_user'@'192.168.175.%' identified by' 123456'

Query OK, 0 rows affected (0.00 sec)

Mysql > flush privileges

Query OK, 0 rows affected (0.00 sec)

3. Lock the table backup on the main library and unlock it.

Lock the table, after locking the table, the current window cannot be closed temporarily:

Mysql > flush tables with read lock

Query OK, 0 rows affected (0.01 sec)

View master status information:

Mysql > show master status

+-+

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

+-+

| | mysql-bin.000001 | 414 |

+-+

1 row in set (0.00 sec)

Create a new ssh window and back up the database:

[root@Mysql-248 ~] # mysqldump-uroot-p 'qwe123``-A > / tmp/master248.sql

When the backup is complete, unlock it in the previous window:

Mysql > unlock tables

Query OK, 0 rows affected (0.00 sec)

Second, slave library configuration:

1. Configure slave libraries server_id and relay-log (restart is required to take effect).

Note: server_id must be unique and cannot be the same as other mysql libraries. There is no need to open binary logs from the library.

[root@Mysql-249 mysql-5.6.30] # grep mysqld-A3 my.cnf

[mysqld]

Port = 3306

Server_id = 2

Relay-log = mysql-relay-bin

two。 Copy the backup of the main library to the local machine and import it into the database.

Copy backup:

[root@Mysql-249 mysql-5.6.30] # scp root@192.168.175.248:/tmp/master248.sql / tmp/

Root@192.168.175.248's password:

Master248.sql

Import:

[root@Mysql-249 mysql-5.6.30] # mysql- uroot-p 'qwe123``

< /tmp/master248.sql Warning: Using a password on the command line interface can be insecure. 3. 指定master服务器信息，开启slave。 指定master信息： mysql>

Change master to\

-> master_host='192.168.175.248'

-> master_user='repl_user'

-> master_password='123456'

-> master_log_file='mysql-bin.000001'

-> master_log_pos=414

Query OK, 0 rows affected, 2 warnings (0.03 sec)

Enable slave:

Mysql > start slave

Query OK, 0 rows affected (0.01 sec)

Third, verify the master-slave replication:

1. Use show slave status\ G in the slave library to query the master library information and the working status of the IO process and the SQL process.

Mysql > show slave status\ G

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.175.248

Master_User: repl_user

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000001

Read_Master_Log_Pos: 414

Relay_Log_File: mysql-relay-bin.000002

Relay_Log_Pos: 283

Relay_Master_Log_File: mysql-bin.000001

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

.

1 row in set (0.00 sec)

The query result shows Slave_IO_Running: Yes,Slave_SQL_Running: Yes, indicating that the current master-slave replication status is normal.

two。 Create a new database in master, query in slave, and test the effect of master-slave replication.

Master to build database and table.

Mysql > create database cubix character set utf8

Query OK, 1 row affected (0.00 sec)

Mysql > use cubix

Database changed

Mysql > create table T1 (id int)

Query OK, 0 rows affected (0.02 sec)

Mysql > insert into T1 VALUES ('1')

Query OK, 1 row affected (0.00 sec)

Mysql > insert into T1 VALUES ('2')

Query OK, 1 row affected (0.00 sec)

Mysql > insert into T1 VALUES ('3')

Query OK, 1 row affected (0.01sec)

Slave queries the newly created library.

Mysql > show databases

+-+

| | Database |

+-+

| | information_schema |

| | cubix |

| | mysql |

| | performance_schema |

+-+

6 rows in set (0.00 sec)

Mysql > use cubix

Database changed

Mysql > show tables

+-+

| | Tables_in_cubix |

+-+

| | T1 |

+-+

1 row in set (0.00 sec)

Mysql > select * from T1

+-+

| | id |

+-+

| | 1 |

| | 2 |

| | 3 |

+-+

3 rows in set (0.00 sec)

Check and find that the new data on the master database is also available on the slave database, which can also prove that the master-slave synchronization is normal.

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.