Mysql master-slave deployment and mysql master-slave deployment

Mysql master-slave deployment and mysql master-slave deployment
1. installation environment

1. System Environment

System	IP	Host Name	Description	Server_id
Centos6.7	192.168.0.173	Master	Database: Master	173
Centos6.7	192.168.0.174	Slave	Database: slave	174

2. Manage accounts

Linux Server account/Password	Database Administrator Account Password	Master-slave replication account
Root/zxfly	Zxfly/zxfly	Zxfly_slave/zxfly

3. Check mysql environment requirements

Mysql installation: http://www.cnblogs.com/feiren/p/7697767.html

1) binlog parameters must be enabled for the master database

# View data on the master database
Cat/etc/my. cnf | grep 'Log _ bin'
Log_bin = mysql_bin

2) ensure that the server_id value of the server is unique.

# MySQL-master
Cat/etc/my. cnf | grep 'server-id'
Servers-id = 173
# MySQL-slave
Cat/etc/my. cnf | grep 'server-id'
Servers-id = 173

3) ensure that the server runs properly

# Monitoring methods:
Ps-ef | grep mysql
Ss-lntup | grep 3306
Service mysqld status

Ii. deployment process # operations on the master

Log on to mysql as a root user:

Mysql-uroot-pup366.com

1. Add and authorize the master-slave replication account

Mysql> grant replication slave on *. * to 'zxfly _ slave '@ '2017. 192. %' identified by 'zxfly ';
Mysql> flush privileges;

2. view the current location information of the database

# The location point you can view must be recorded. It will be used below
Mysql> show master status;

# If the master and slave nodes are not in the newly deployed environment, perform the following operations. Otherwise, skip

3. Full backup database

Mysqldump-uroot-pzxfly-A-B-R -- master-data = 2 -- events -- single-transaction>/root/all. SQL

4. Send full backup to slave Database

Scp/root/all. SQL 192.168.0.174:/root/

# Operations on slave

# If the master and slave nodes are not in the new deployment environment, perform Step 1. Otherwise, Skip.

1. Import Full backup to import the full backup of the master database to the slave database.

Mysql-uroot-pzxfly </root/all. SQL

2. log on to the database of the slave database and enter the account location of the synchronization Information

Change master
Master_host = '192. 168.0.173 ', # IP address of the host where the master database is located
Master_port = 3306, # master database Port
Master_user = 'zxfly _ slave ', # authorized synchronization account
Master_password = 'zxfly ', # authorization account password
MASTER_LOG_FILE = 'mysql _ bin.000001 ', # view the latest binlog on the master database
MASTER_LOG_POS = 120; # view the latest location on the master database

3. Start Synchronization

Mysql> start slave;

Iii. Verification

1. Run the slave Database

Mysql-uroot-p-e "show slave status \ G" | egrep 'slave _ IO_Running: | Slave_ SQL _Running :'

# If two yes values are filtered out and the synchronization is successful

2. You can perform a test on the master database. The test mainly involves adding, deleting, modifying, and checking whether the master database is synchronized.

3. error message Overview

Error code:

2003 # Check whether the master-slave replication account is incorrect and whether the firewall is allowed to pass

1045 # Check whether account permissions are normal

1236 # Check whether the binlog location is incorrect and whether the binlog name is incorrect