MySQL master-slave replication operation
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.
