MySQL master-slave config. txt

Preparation session:

master, slave server operating system, MySQL version must be consistent

First, the environment

Host:

Master Operating system: CentOS 5.5 64-bit

ip:192.168.3.28

MySQL version: 5.5.17

Slave machine:

Slave operating system: CentOS 5.5 64-bit

ip:192.168.3.108

MySQL version: 5.7.17

Second, the Master machine and slave machine related configuration

1, create a replication user, with replication slave permissions.
Mysql>grant replication Slave on * * to ' WXL ' @ ' 192.168.3.108′identified by ' 123456 '

2, modify the master machine MySQL configuration file my.cnf, the file in/etc directory
If the/etc/directory does not have a my.cnf profile, go to/usr/share/mysql/to locate the my-medium*.cnf file, copy it to/etc/and rename it to MY.CNF
Commands such as: # CP/USR/SHARE/MYSQL/MY-MEDIUM.CNF/ETC/MY.CNF

Add the following field to the [mysqld] configuration segment
Log-bin = mysql-bin
Log-bin-index = mysql-bin.index
Binlog_format = row
Server-id = Sync-binlog
= 1
character-set-server = UTF8
Description:
Log-bin―― Set the base name of the binary log file;
Log-bin-index sets the binary log index file name. The
Binlog_format―― controls the binary log format, which in turn controls the type of replication, three optional values, STATEMENT: statement copy, logical DG similar to Oracle,
Row copy, physical DG similar to Oracle;
MIXED: Mixed copy, default option. To avoid inconsistent master-slave data, it is recommended that you modify it to row.
Server-id―― sets a unique ID for the server, the default is 1, the last part of the IP is recommended, and
Sync-binlog―― defaults to 0, to ensure that no data loss is required, set to 1 to force the synchronization of binary logs to disk each time a transaction is committed.
Note: These parameters are required, please refer to the relevant documentation for additional parameters

3, restart the master machine MySQL service: Service mysqld restart
If the master library can be shut down, all database files can be copied directly, and if the master library is an online production library and cannot be shut down, it can be fully backed up in a variety of ways, preferably with mysqldump because it is available to all storage engines.
--To get a consistent snapshot, you need to set read locks on all tables
Mysql> flush tables with read lock;
Query OK, 0 rows Affected (0.00 sec)
--Get the offset of the binary log
Mysql> Show master status;
+------------------+----------+--------------+------------------+
| File | Position | binlog_do_db | binlog_ignore_db |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 107 | | |
+------------------+----------+--------------+------------------+
1 row in Set (0.00 sec)

--Backing up the database
For transactional engines (such as: InnoDB), you can use the--single-transaction parameter to get a consistent snapshot.
# mysqldump-uroot-proot123--all-database-e--single-transaction--flush-logs--max_allowed_packet=1048576--net_ Buffer_length=16384>/data/all_db.sql

For MyISAM engines, or multi-engine mixed databases, you can use the-l parameter to lock the table for a consistent backup
# mysqldump-uroot--ALL-DATABASE-E-L--flush-logs--max_allowed_packet=1048576--net_buffer_length=16384>/data/ All_db.sql

Note: Because we have read locks on all tables in advance using the flush tables with read lock command, there is no need to use the-l parameter to lock again.

-After the backup is complete, copy the backup files to the slave
# SCP All_db.sql [email protected]:/data/

--Restore Master Library write operations
mysql> unlock tables;
Query OK, 0 rows Affected (0.00 sec)

In fact, Mysqldump also provides a--master-data parameter, the backup file contains the change master to statement, in order to clearly understand the master-slave replication process, we do not use this parameter.

Second, from the server

1. Edit the my.cnf file in the [mysqld] group to add the following settings

Log-bin =/data/mysql/mysql-bin
Binlog_format = row
Log-slave-updates = 1
Server-id = 108
Relay-log = Mysql-relay-bin
Relay-log-index = Mysql-relay-bin.index
Read-only = 1
Slave_net_timeout =10
replicate_do_db =db a database that needs to be synchronized
Description
1) relay-log―― set the trunk log file base name; Relay-log-index set the trunk log index file name.
2) read-only―― settings Slave is read-only, but users with super privileges are still writable.
3) log-slave-updates―― This parameter is used to control whether the update on the slave is written to the binary log, the default is 0, if the slave only as the slave server, it does not have to be enabled, if slave as the master of other servers, it needs to be enabled and Log-bin , Binlog-format is used together, so that slave reads the log from the main library and then logs it to its own binary log.
4) slave_net_timeout―― Set the network timeout time, that is, how long to test the master-slave connection, the default is 3,600 seconds, that is, 1 hours, this value in the production environment is too large, we will modify it to 10 seconds, even if master-slave interrupt 10 seconds, then trigger the reconnection action.

2, restore slave
Restore the Master library backup to the slave library, the method is relatively simple, directly execute.
# mysql-uroot-proot123 </data/all_db.sql

Description: The Skip-slave-start function is to start the copy process from the library but not immediately start from the library, in order to set further from the library.

2. Start from the database
Service MySQL Restart

3, the corresponding settings from the database
#如果出现出现这个错误: ERROR 1201 (HY000): Could not initialize master! need to stop slave process
mysql> slave stop;
Query OK, 0 rows affected, 1 Warning (0.00 sec)
mysql> Reset Slave;
Query OK, 0 rows affected, (0.00 sec)

Mysql> Change Master to
Master_host= ' 192.168.3.28 ',
Master_user= ' WXL ',
-master_password= ' 123456 ',
Master_log_file= ' mysql-bin.000003 ',
master_log_pos=107;
Query OK, 0 rows affected (0.01 sec)

4. Start slave thread from server
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

The Execute show processlist command displays the following processes:
Mysql> Show Processlist \g
1. Row ***************************
Id:2
User:root
Host:localhost
Db:null
Command:query
time:0
State:null
Info:show processlist
2. Row ***************************
Id:3
User:system User
Host:
Db:null
Command:connect
Time:21
State:waiting for Master to send event
Info:null
3. Row ***************************
Id:4
User:system User
Host:
Db:null
Command:connect
Time:21
State:slave have read all relay log; Waiting for the slave I/O thread to update it
Info:null
3 Rows in Set (0.00 sec)

5. View slave thread Status
Mysql> Show Slave status \g
1. Row ***************************
Slave_io_state:waiting for Master to send event
master_host:192.168.3.28
Master_user:wxl
master_port:3306
Connect_retry:60
master_log_file:mysql-bin.000009
read_master_log_pos:107
relay_log_file:mysql-relay-bin.000014
relay_log_pos:253
relay_master_log_file:mysql-bin.000009
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:107
relay_log_space:452
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:28
1 row in Set (0.00 sec)
Description
Slave_io_running―― This process is responsible for reading Binlog logs from Slave from master and writing to the relay log on Slave.
Slave_sql_running―― This process is responsible for reading and executing the Binlog log in the trunk log.
The status of both processes must be all yes, and if one is no, replication stops.
When Relay_master_log_file and Master_log_file are the same in slave, and Read_master_log_pos and Exec_master_log_pos are exactly the same, Indicates that slave and master are in a fully synchronized state.

Third, testing
1, create a test table in the Master library, and insert data;
Mysql> CREATE TABLE Test3 as SELECT * from Test2;

2. Then check the slave library to see if the copy is correct
mysql> use test;
Database changed
Mysql> Show tables;
+----------------+
| Tables_in_test |
+----------------+
| Test1 |
| Test2 |
| Test3 |
+----------------+
3 Rows in Set (0.00 sec)

Mysql> select * from Test3;
+-----------+----------+
| User_name | User_pwd |
+-----------+----------+
| Zhangsan | 123456 |
| Lisi | 123456 |
| Wangwu | 123456 |
| Zhaoliu | 123456 |
| Xiaoqian | 123456 |
+-----------+----------+
5 rows in Set (0.00 sec)

Description
1) visible from the server has correctly replicated the master server data, master-slave replication is correctly configured successfully.
2) But the read-only parameter does not work, because we are logged in with the root account, it has super privileges, and is replaced by the normal account test, which is not granted the super or all permissions of the account, the test is as follows:
mysql> Use test
Database changed
Mysql> select * from test;
+------+--------+
| ID | name |
+------+--------+
| 6 | FFFFFF |
| 7 | GGGGGG |
| 8 | Hhhhhh |
+------+--------+
3 Rows in Set (0.00 sec)

mysql> Delete from test;
ERROR 1290 (HY000): The MySQL server is running with the--read-only option so it cannot execute this statement
The visible prompt is read-only and cannot be deleted.

MySQL master-slave config. txt