CentOS6.5 configuration MySQL One master more from the detailed

First, the environment

1 6.5 2 5.6.  - 3 Host A:192.168. 1.1 (Master) 4 slave B:192.168. 1.2 (Slave) 5 slave B:192.168. 1.3 (Slave)

Second, the basic Environment configuration

MySQL is accessed via port 3306, so make sure the firewall is on for Port 3306.

Third, the configuration of master

1 , configuration my.cnf

In the Linux environment, the location of the MySQL configuration file is in/etc/my.cnf, under which the configuration of master is specified as follows:

[[email protected] ~]# VIM/etc/My.cnfserver-Id= 1# generally with the last paragraph of IP, this identity is uniqueLog-Bin=Mysql-Binbinlog-Ignore-Db=Test # Exclude out-of-sync library Binlog-Ignore-Db=Mysqlbinlog-Ignore-Db=Performance_schemabinlog-Ignore-Db=Information_schemabinlog-Do-Db=vertical_analysis # Select the library to synchronize Binlog_format=mixedexpire_logs_days= 7Relay-Log               =Relay-LogLog_slave_updatesskip-Slave-Start

Description

BINLOG-IGNORE-DB: Indicates the Ignore database when synchronizing

BINLOG-DO-DB: Specify the database that needs to be synchronized

2 , restart MySQL

[Email protected] ~]# service mysqld restart

3 , enter MySQL

[Email protected] ~]# mysql-u root-p

4 , assigning permissions from the Vault account

Allow the user to read the log on the main library, give 192.168.1.2/3 that is the slave machine has file permissions, only give slave machine has file permissions, but also to give it replication slave permission to.

In the master database command line, enter:

Mysql> GRANT FILE  on *.*  to 'slaver'@'%'Identified by '123456'; MySQL> GRANT REPLICATIONSLAVE on *.*  to 'slaver'@'%'Identified by '123456'; MySQL>FLUSHPrivileges

Here slaver the user as the time of synchronization, you can set it yourself.

5 , testing authorization results

MySQL>Select*fromuserwhere host=' % '  and User = ' Tantuls ' # If the Repl_slave_priv entry is Y, the authorization is successful. 

6 , view master database Master status

Mysql>show master status;+------------------+----------+-----------------------+---------------------------------------------------+--- ----------------+| File             |Position|binlog_do_db|binlog_ignore_db|Executed_gtid_set|+------------------+----------+-----------------------+---------------------------------------------------+--- ----------------+|Mysql-Bin.000004 |    28125 |Vertical_analysis|Performance_schema, Information_schema,test,mysql|                   |+------------------+----------+-----------------------+---------------------------------------------------+--- ----------------+1Rowinch Set(0.00Sec

Here the File, Position is to be used when configuring Salve, binlog_do_db represents the database to be synchronized, binlog_ignore_db represents the Ignore of the database, these are configured at the time of the specified.

Iv. Configuration of slave

1 , configuration my.cnf

[[email protected] ~]# VIM/etc/My.cnfserver-Id= 2# generally with the last paragraph of IP, this identity is uniqueLog-Bin=Mysql-Binbinlog_format=mixedexpire_logs_days= 7Relay-Log               =Relay-Log # Note: Different slave are also the only binlog here -Ignore-Db=Information_schemabinlog-Ignore-Db=Performance_schemabinlog-Ignore-Db=Mysqlbinlog-Ignore-Db=TestReplicate-Do-Db=vertical_analysisReplicate-Ignore-Db=Mysqllog_slave_updatesskip-Slave-Startslave-Skip-Errors=  Allslave-Net-Timeout=  -

2 , configuring slave synchronization information

Mysql>Stop Slave;mysql>Change Master toMaster_host='192.168.1.1', Master_port=3306, Master_user='slaver', Master_password='123456', Master_log_file='mysql-bin.000004', Master_log_pos=28125; MySQL>Start slave;

3 , detecting replication capability status from the database

MySQL>  Show Master status\g;  # Each of the following two items must be yes. Slave_IO_Running:YesSlave_SQL_Running:Yes 

V. Verification

Build a table on the main library to see if the library is synchronized.

Report:

Error 1:slave failed to initialize relay log info structure from the repository

Workaround: use reset slave all to clear all replication information and then reset the Master.infor, copy normal after start slave.

CentOS6.5 configuration MySQL One master more from the detailed