MySQL master-slave replication case

MySQL master-slave replication case

Working principle diagram:

Principle of master-slave replication:
It can be divided into synchronous replication and asynchronous replication. In the actual replication architecture, most of them are asynchronous replication. The basic process of replication is as follows:
1). the IO process on the Slave connects to the Master and requests the log content after the specified location of the specified log file (or from the initial log;
2 ). after the Master receives a request from the Slave IO process, it reads the log information after the specified location of the log based on the request information by the IO process responsible for replication and returns it to the Slave IO process. Besides the information contained in the log, the returned information also includes the name of the bin-log file on the Master end and the location of the bin-log;
3 ). after the Slave IO process receives the information, it adds the received log content to the end of the relay-log file at the Slave end in sequence, and record the file name and location of the bin-log on the Master end to the master-info file, in this way, you can clearly tell the Master "from where a bin-log is to be read, send it to me ";
4 ). after the Slave SQL process detects the newly added content in relay-log, it will immediately parse the relay-log content into the executable content during actual execution on the Master end, and execute it on your own.
Environment Description
Operating System: CentOS6.3 _ x64
Master server: 192.168.0.202
Slave server slave: 192.168.0.203

I. mysql master-slave Replication
1. Install mysql on the master and slave nodes in the same version.
We installed mysql-5.5.30.tar.gz, Which is omitted here... please refer

2. Modify the master and slave servers

Master Server Configuration:
Vi/usr/local/mysql/etc/my. cnf
[Mysqld]
Server-id = 202 # Set the unique id of the server. The default value is 1. We set the last segment of the ip address, and set slave to 203.
Log-bin = mysql-bin # enable binary log
Binlog-ignore-db = mysql, information_schema # ignore the database written to binlog

Slave Server Configuration:
Vi/usr/local/mysql/etc/my. cnf
[Mysqld]
Servers-id = 203
Replicate-do-db = abc # synchronize only the abc Library
Slave-skip-errors = all # ignore all errors caused by Replication

3. Restart the master-slave mysql server.

/Etc/init. d/mysqld restart

4. Create an account on the master server and authorize slave

Mysql> mysql-u root-p123.com
Mysql> grant replication slave on *. * to 'sync' @ '192. 168.1.2 'identified by '192. com'; # replication: REPLICATION

5. view the status of the primary database

Mysql> show master status;
+ ------------------ + ---------- + -------------- + ------------------ +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+ ------------------ + ---------- + -------------- + ------------------ +
| Mysql-bin.000002 | 263 |
+ ------------------ + ---------- + -------------- + ------------------ +

6. Configure slave Database

Mysql> change master
-> Master_host = '192. 168.0.202 ',
-> Master_user = 'sync ',
-> Master_password = '2014. com ',
-> Master_log_file = 'mysql-bin.000002 ',
-> Master_log_pos = 263;
# Log and pos are randomly obtained on the master node. This section can also be written to my. cnf.

7. Start the slave synchronization process and check the status

Mysql> start slave;

The values of Slave_IO_Running and Slave_ SQL _Running must be YES to indicate that the status is normal.
8. Verify master-slave Synchronization
Create a database abc in the primary mysql and check that the synchronization is successful from mysql!

Mysql> create database abc;
Mysql> show databases;
+ -------------------- +
| Database |
+ -------------------- +
| Information_schema |
| Abc |
| Mysql |
| Performance_schema |
| Test |
+ -------------------- +

When slave is enabled, the following error occurs:
"Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file '"
Solution: the error is mainly caused by the mismatch of the binary file name or pos value of the master set by slave!
First flush logs; clear the log, and check the status of the master database: show master status; check the log file name and position;
Then, in slave, execute: change master to MASTER_LOG_FILE = 'binary log name', MASTER_LOG_POS = value;
Start the synchronization process: start slave;

-------------------------------------- Split line --------------------------------------

Load Nginx in Ubuntu for high-performance WEB Server 5 --- MySQL master/Master Synchronization

Production Environment MySQL master/Master synchronization primary key conflict handling

MySQL Master/Slave failure error Got fatal error 1236

MySQL master-slave replication, implemented on a single server

Build a MySQL proxy server for read/write splitting + Master/Slave Synchronization

MySQL 5.5 master-slave bidirectional Synchronization

MySQL 5.5 master-slave synchronization troubleshooting

MySQL master-slave replication asynchronous semi-sync instance

-------------------------------------- Split line --------------------------------------

This article permanently updates the link address: