Mysql master-slave copy notes under Centos6.3

The MySQL master-slave replication structure is based on the mysql bin-log. The slave database receives the bin-log incremental information of the master database by opening the IO process and saves it to the local relay log, then, the incremental information obtained from the relay log by opening the MYSQL process and translating it into an SQL statement and writing it to the slave database.

The master-slave replication structure can actually implement two functions

1. Back up an instance from a database that acts as the master database

2. read/write splitting the master database is responsible for reading and writing data normally. The slave database is only responsible for reading data.

In the actual production environment, because many applications actually read databases much more frequently than the number of database writes, a program is written at the initial stage of project development to determine that all read operations are pushed to the slave database. if you cannot obtain data, you can obtain the data from the master database to implement read/write splitting to relieve the I/O pressure on the master database.

This architecture is recommended in the production environment.

System Environment centos6.3 x64

Database mysql-5.6.10

Mysql master: 192.168.100.90

Mysql slave: 192.168.100.91

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131228/2233303Y7-0.jpg "title =" mysqlmaster copy .jpg "alt =" 091218484.jpg"/>

I. Deployment Environment

1. Disable iptables and SELINUX

# Service iptables stop

# Setenforce 0

# Vi/etc/sysconfig/selinux

---------------

SELINUX = disabled

---------------

2. install and configure the mysql portal http://showerlee.blog.51cto.com/2047005/1174141

Ii. master mysql configuration: (mysql master)

Modify the mysql configuration file

# Vi/etc/my. cnf

Add

-----------------

# Replication Master Server

# Bin Log Path

Log-bin =/usr/local/mysql/log/bin. log

# Server ID

Server-id = 1

# Ignore mysql System Database Replication

Binlog-ignore-db = mysql

Binlog-ignore-db = information_schema

------------

Restart service

# Service mysqld restart

Log on to the mysql background

# Mysql-u root-p123456

View the current Logon account

> Select user ();

Add a synchronization account for slave on the master

> Grant replication slave on *. * to 'slave '@ '192. 168.100.91' identified by '123 ';

View created users

> Select user. host from mysql. user;

View Permissions

> Show grants for 'slave '@ '192. 168.100.91 ';

Mysql lock table read-only (Other accounts cannot write tables after logging on to mysql to prevent updates to the master mysql table after the database is backed up)

> Flush tables with read lock;

View lock table Countdown time

> Show variables like '% timeout % ';

------------------------

....

Wait_timeout | 28800

------------------------

Back up and export all the database tables of the master node and transmit them to the slave server.

#/Usr/local/mysql/bin/mysqldump-u root-p123456 -- opt -- flush-logs -- all-database>/root/allbak. SQL

# Cd ~

# Scp allbak. SQL root@192.168.100.91:/root

View the mysql offset (the database will increase progressively if there is a write operation offset)

# Mysql-u root-p123456-e "show master status"

----------------------

+ ------------ + ---------- + -------------- + ------------------ + ------------------- +

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+ ------------ + ---------- + -------------- + ------------------ + ------------------- +

| Bin.000009 | 120 |

+ ------------ + ---------- + -------------- + ------------------ + ------------------- +

----------------------

Ensure that the FILE column and Position column are consistent with the slave database configuration.

3. Configure mysql slave from mysql

Modify the mysql configuration file

# Vi/etc/my. cnf

Add

--------------------

# Replication Slave Server

# Bin-log is not required in the bin log Path

# Log-bin =/usr/local/mysql/log/bin. log

Server-id = 2

# Read-Only

Read-only

# Ignore mysql System Database Replication

Binlog-ignore-db = mysql

Binlog-ignore-db = information_schema

---------------------

Restart service

# Service mysqld restart

Restore the server database to slave

#/Usr/local/mysql/bin/mysql-u root-p123456 </root/allbak. SQL

Configure the connection to synchronize to the server

# Mysql-u root-p123456;

> Stop slave;

> Reset slave;

> Change master to master_host = '192. 168.100.90 ', master_user = 'slave', master_password = '000000', master_log_file = "bin.000009", master_log_pos = 192;

> Start slave;

Note: master_log_file indicates which bin-log file of the master database is synchronized.

Master_log_pos indicates which record point of the bin-log file to start synchronization.

Synchronization with the Offset Value of the master database

Return to the primary mysql database to unlock the mysql master.

# Mysql-u root-p123456

> Unlock tables;

Finally, log on to the mysql background to view the master-slave connection status.

# Mysql-u root-p123456-e "show slave status \ G ;"

If you find the following five lines, the Master/Slave configuration is successful.

------------------------

Slave_IO_State: Waiting for master to send event

Slave_IO_Running: Yes

Slave_ SQL _Running: Yes

Read_Master_Log_Pos: 120

Relay_Master_Log_File: bin.000009

-------------------------

Iv. test whether the master and slave nodes are synchronized

(Server)

# Mysql-u root-p123456-e "create database test02 ;"

# Mysql-u root-p123456-e "show databases like 'test02 ';"

------------

+ ------------------- +

| Database (test02) |

+ ------------------- +

| Test02 |

+ ------------------- +

------------

(Cilent)

# Mysql-u root-p123456-e "show databases like 'test02 ';"

-------------

+ ------------------- +

| Database (test02) |

+ ------------------- +

| Test02 |

+ ------------------- +

-------------

The test is successful ..

When the database data on the server changes, the client synchronously updates the data to implement the backup and read/write splitting of the master database.

Notes

1. the read-only parameter must be added to the my. cnf configuration file in the mysql slave database to ensure that the slave database is read-only.

# Echo "read-only">/etc/my. cnf

2. Ignore synchronization between the slave database mysql and the information_schema system table

# Echo "binlog-ignore-db = mysql">/etc/my. cnf

# Echo "binlog-ignore-db = information_schema">/etc/my. cnf

Authorize the master database user in the production environment to add, delete, modify, and query permissions.

> Grant select, INSERT, UPDATE, delete on *. * to 'user' @ '% 'identified by '123 ';

Authorization of the slave database in the production environment. Only the query permission is authorized.

> Grant select on *. * to 'user' @ '%' identified by '123 ';

3. The bin-log function is disabled by default in the slave database. The bin-log function of the slave database must be enabled only when the sub-database cascade synchronization is performed.

4. How to upgrade the master database to the master database due to hardware faults (one master multiple slaves)

(Mysql slave)

(1) The Has read all relay log occurs when no SQL statement is synchronized from the slave machine, and then the slave database IO_Threat process is disabled.

# Mysql-uroot-p123456

> Stop slave IO_THREAD

(2) disable the slave database slave service and promote it to the master database

> Stop slave

> Reset master

(3) change the slave database IP address to the IP address of the faulty master database (configuration method omitted)

(4) Delete the new master database master.info and relay-log.info, to prevent the next restart will follow the slave database to start

# Cd/usr/local/mysql/log

# Rm-rf master.info relay-log.info

(5) Reconfigure the account synchronization information for the slave database to connect to the master database, and reset the offset in the slave database to keep consistent with the new master database.

Finally, after the master database hardware is restored, it is set to slave database and replaced with the slave database IP address.

5. Configure scripts

1. If you want to implement unattended backup of the master database, you can add the following script and execute the scheduled task in the early morning.

# Vi/etc/rc. d/mysql_bak.sh

---------------------

#! /Bin/sh

MYSQL_USER = root

MYSQL_PW = "123456"

LOG_PATH =/usr/local/mysql/log

DATA_PATH =/usr/local/mysql/data

LOG_FILE =$ {LOG_PATH}/mysqllog _ 'date + % F'. log

DATA_FILE =$ {DATA_PATH}/mysql_backup _ 'date when using f'. SQL .gz

BIN_PATH =/usr/local/mysql/bin

MYSQL_CMD = "$ BIN_PATH/mysql-u $ MYSQL_USER-p $ MYSQL_PW"

MYSQL_DUMP = "$ BIN_PATH/mysqldump-u $ MYSQL_USER-p $ MYSQL_PW -- opt -- flush-logs -- all-database"

$ MYSQL_CMD-e "flush tables with read lock ;"

Echo "------- show master status result -------"> $ LOG_FILE

$ MYSQL_CMD-e "show master status"> $ LOG_FILE

$ {MYSQL_DUMP} | gzip> $ DATA_FILE

$ MYSQL_CMD-e "unlock tables"

Mail-s "mysql slave log" 1234567@qq.com <$ LOG_FILE

---------------------

Back up the database at AM.

# Crontab-e

---------------------

30 3 ***/bin/sh/etc/rc. d/mysql_bak.sh>/dev/mull 2> & 1

---------------------

# Service crond restart

2. If you want to distribute the data backed up by the master database to restore the slave database from the slave database and enable the slave database function, add the following script:

Note: We recommend that you authenticate the master key first.

See portal http://showerlee.blog.51cto.com/2047005/1217651 for details

# Vi/etc/rc. d/mysql_bak1.sh

------------------

#! /Bin/sh

MYSQL_USER = root

MYSQL_PW = "123456"

MYSQL_SLAVE_IP = "192.168.100.91"

SSH_PATH = "/usr/bin/ssh"

SSH_CMD = "$ {SSH_PATH }$ {MYSQL_SLAVE_IP }"

GZIP_CMD = "/bin/gzip"

LOG_PATH =/usr/local/mysql/log

DATA_PATH =/usr/local/mysql/data

LOG_FILE =$ {LOG_PATH}/mysqllog _ 'date + % F'. log

DATA_FILE =$ {DATA_PATH}/mysql_backup _ 'date when using f'. SQL .gz

BIN_PATH =/usr/local/mysql/bin

MYSQL_CMD = "$ BIN_PATH/mysql-u $ MYSQL_USER-p $ MYSQL_PW"

MYSQL_DUMP = "$ BIN_PATH/mysqldump-u $ MYSQL_USER-p $ MYSQL_PW -- opt -- flush-logs -- all-database"

$ MYSQL_CMD-e "flush tables with read lock ;"

Echo "------- show master status result -------"> $ LOG_FILE

$ MYSQL_CMD-e "show master status"> $ LOG_FILE

$ {MYSQL_DUMP} | gzip> $ DATA_FILE

# Config slave

Cd $ {DATA_PATH}

Scp "mysql_backup _ 'date when using f'. SQL .gz" $ MYSQL_SLAVE_IP:/tmp/

$ {SSH_CMD} "$ {GZIP_CMD}-d/tmp/mysql_backup _ 'date when using f'. SQL .gz"

$ {SSH_CMD} "$ {MYSQL_CMD} </tmp/mysql_backup _ 'date + % F'. SQL"

$ {SSH_CMD} "cat | $ MYSQL_CMD" <EOF

Stop slave;

Change master

Master_host = '1970. 168.100.90 ',

Master_user = 'slave ',

Master_password = '000000 ',

Master_log_file = "bin.000009 ",

Master_log_pos = 120;

Start slave;

EOF

$ SSH_CMD $ MYSQL_CMD-e "show slave status \ G;" | egrep "IO_Running | SQL _Running" >>> $ LOG_FILE

$ MYSQL_CMD-e "unlock tables"

Mail-s "mysql slave log" 1234567@qq.com <$ LOG_FILE

$ SSH_CMD mail-s "mysql slave log" 1234567@qq.com <$ LOG_FILE

------------------

Mysql Master/Slave my. cnf parameter configuration:

--------------------------------

[Mysqld]

Port = 3306

Datadir =/usr/local/mysql/data

Socket =/var/lib/mysql. sock

User = mysql

Symbolic-links = 0

Max_connections = 16384

Skip-external-locking

Skip-name-resolve

Key_buffer_size = 256 M

Query_cache_limit = 1 M

Query_cache_size = 64 M

Max_allowed_packet = 4 M

# Table_cache = 8

Thread_concurrency = 8

Sort_buffer_size = 8 M

Read_buffer_size = 4 M

Read_rnd_buffer_size = 4 M

Net_buffer_length = 1 M

Thread_stack = 1 M

Log-error =/usr/local/mysql/log/error. log

Log =/usr/local/mysql/log/mysql. log

Long_query_time = 2

Log-slow-queries =/usr/local/mysql/log/slowquery. log

# Server ID number (MASTER: 1 and slave: 2)

Server-id = 1

# Ignore mysql System Database Replication

Binlog-ignore-db = mysql

Binlog-ignore-db = information_schema

Log-bin =/usr/local/mysql/log/bin. log

# Delete binary logs generated 10 days ago

Expire_logs_days = 10

[Mysqldump]

Quick

Max_allowed_packet = 16 M

[Mysql]

No-auto-rehash

# Safe-updates

[Isamchk]

Key_buffer = 8 M

Sort_buffer_size = 8 M

[Myisamchk]

Key_buffer = 8 M

Sort_buffer_size = 8 M

[Mysqlhotcopy]

Interactive-timeout

--------------------------------

------- Success ---------

This article from "all the way to the North" blog, please be sure to keep this source http://showerlee.blog.51cto.com/2047005/1220801