Introduction to MySQL master-slave replication

Reference: https://my.oschina.net/u/255939/blog/505598

MySQL replication is a MySQL server (slave) that replicates logs from another MySQL server (master) and then parses the logs and applies them to itself, similar to data Guard in Oracle.

MySQL replication has those benefits:

• The first is to solve the data inconsistency caused by the outage, because MySQL replication can back up data in real time;

• The 2nd is to reduce the pressure on the database server, the performance of multiple servers is generally better than a single one. But MySQL replication is not suitable for large data volumes, and large data volumes are recommended for use in clusters.

The MySQL replication process is divided into three steps:

• Master will change the record to binary log (binary logs). These recording processes are called binary log events, binary logs event;

• The slave copies the binary log events of master to its trunk log (relay log);

• Slave redo the events in the trunk log and apply the changes to your own database.

MySQL replication is asynchronous and serialized

MySQL master-slave copy Build

Environment Preparation

Host win ten ip:10.22.75.158

Slave ubuntu 15.0.4 ip:192.168.137.128

1. Configuration of the host

Locate the MySQL configuration file My.ini and add the following to it, [MySQL] and [mysqld] are to be added

Max_binlog_size represents the largest binary file size, expire_logs_days is the log expiration time

Server-id is the host ID requirement that must be unique

BINLOG-DO-DB,BINLOG-IGNORE-DB is to synchronize the database and ignore the database, if you do not configure the two should be all databases under MySQL synchronization, including the creation of the database, I am here to configure the test database (MySQL login command Mysql-u root-p Return and enter the password)

[mysql]default-character-set=utf8[mysqld]log-bin="C:/Program Files/MySQL/MySQL Server 5.5/binlog"expire_logs_days=10max_binlog_size=100Mserver-id=1binlog-do-db=testbinlog-ignore-db=mysql

After configuration, you can see if the effect is

You can see that Log_bin is on

Configure the account required for replication on master and% indicates that the root user of any remote address can connect to the master host

GRANT replication slave ON *.*TO [email protected]‘%‘ IDENTIFIED BY ‘gqb101112‘;

Identified is the password.

In the host creation test database, a table GQB

The structure of the primary table is this way.

Configuration of MySQL slave machine

Installation of 1.mysql

Just loaded Ubuntu run Apt-get update to update the package list before running the command apt-get mysql-server mysql-client to install MySQL

2. Configure the Service ID

[mysqld]server-id=2character_set_server=utf8[client]default-character-set=utf8

Slave can not configure Bin-log can also be configured to enter MySQL

The position above master_log_pos=4118 is the value of the position field from the image below.

Master_log_file can view the file name in the host through show Master status.

Then turn on the slave's slave to run command line start slave, and then through show slave status\g, to view the status

The underlined is the two threads that MySQL opened, respectively, to read the binary files on the host and execute MySQL. Next, insert the data on the host, and then see if the data from the machine has been updated, and the result is the following three images

Problems encountered during configuration

Is the Ubuntu table field of the character set is not UTF8 causes the master-slave replication error, resulting in interrupt synchronization;

First, the Ubuntu character set is configured as H_cn.utf8

1. View the character set supported by the system
Locale-a
Results:
Feng@feng: ~$ locale-a
C
En_au.utf8
En_bw.utf8
En_ca.utf8
En_dk.utf8
En_gb.utf8
En_hk.utf8
En_ie.utf8
En_in
En_nz.utf8
En_ph.utf8
En_sg.utf8
En_us.utf8
En_za.utf8
En_zw.utf8
Posix
Zh_cn.utf8
Zh_hk.utf8
Zh_sg.utf8
Zh_tw.utf8


2, modifying the character set
Export.utf8

After modification, you can view the current character set by locale

How to modify the character set of a field: http://blog.csdn.net/lyjtynet/article/details/6261066

How to modify the character set for Ubuntu MySQL configuration: http://blog.csdn.net/huazhizui/article/details/8637002

Introduction to MySQL master-slave replication