MYSQL----AB replication (master-slave replication)

AB replication (master-slave replication)

you can have MySQL implement:
1. Data backup (master server data is all synchronized to slave server)
2. Can load balance reduce the pressure of the primary server
3. True MySQL read and write separation

Configuration Method
Primary server:
1. Primary server MY.CNF Configuration
cat/etc/my.cnf
[Mysqld]
Log-bin=mysql_bin
server-id=1 not allowed to repeat
2. Authorization, specified from the server can only replicate Binlog, IP from the server
Grant Replication Slave on * * to ' slave ' @ ' 192.168.1.140 ' identified by ' 123 ';
to view the health status of the primary server
mysql> Show Master Status

3. Use the Slave user login test on the slave:
mysql-u slave-p123-h 192.168.1.14

4. Master Service execution data fully prepared
mysqldump--all-database > Db_backup.sql
5. Locate the current log file and location number
mysql> Show master status;

from server:(can not turn on binlog function)
6. Copy the full backup script of the master service to the master server and import the master server
# mysql-uroot-p123 < db_backup.sqlb
7. MY.CNF Configuration from server
cat/etc/my.cnf
[Mysqld]
server-id=2 and the primary server do not have to be duplicated
master-host=192.168.1.131 Primary server IP
master-user=slave account at login
master-password=123 MySQL reads the configuration file, it knows it is running from the server

8. When you read binlog from the server from the primary server to generate problems
mysql-u root-p
mysql> Change Master to
mysql> master_host= ' 192.168.1.131 ',
mysql> master_user= ' slave ',
mysql> master_password= ' 123 ',
mysql> master_log_file= ',
mysql> master_log_pos=399;

9. Start the slave server
mysql> slave start or start slave starting from the server
view from server status
mysql> show Slave status

Practice:
Master----from (master)------from
log-slave-updates Middle This function: The trunk log content is written to the Binlog log

Mutual Master
[Mysqld]
Log-bin=mysql_bin
server-id=1
master-host=192.168.100.130
Master-user=slave2
master-password=123
auto-increment-increment=2
auto-increment-offset=1

[Mysqld]
Log-bin=slave
server-id=2
master-host=192.168.100.128
Master-user=slave
master-password=123
auto-increment-increment=2
auto-increment-offset=2

This article is from the "Small Zheng Technology Blog" blog, please be sure to keep this source http://ko178.blog.51cto.com/220419/1765211

MYSQL----AB replication (master-slave replication)