MySQL master-slave replication(super Simple)How to install MySQL database, do not say here, just say its master-slave copy, the steps are as follows:
Note: Install two databases first, and start MySQL, modify the configuration file, restart the database.
1, the master-slave server for the following actions :1.1. Consistent version
1.2. Initialize the table and start MySQL in the background
1.3, change the root password
2. Modify master server Master:
#vi/etc/my.cnf
[Mysqld]
Log-bin=mysql-bin//[must] enable binary logging
server-id=222//[must be] server unique ID, default is 1, usually take IP last paragraph
3. Modify the slave from the server:
#vi/etc/my.cnf
[Mysqld]
Log-bin=mysql-bin//[not required] enable binary logging
server-id=226//[must be] server unique ID, default is 1, usually take IP last paragraph
4. Restart MySQL for two servers
/etc/init.d/mysql Restart
5. Establish an account on the primary server and authorize slave: mysql>GRANT
REPLICATION SLAVE on * * to ' mysync ' @ ' percent ' identified by ' q123456 '; The green section indicates that the server master server is allowed to replicate. //generally do not use the root account,“%” means that all clients may even, as long as the account, the password is correct, here can be replaced by specific client IP, such as 192.168.145.226, enhance security.
6, log on to the master server MySQL, query the status of Mastermysql>show variables like '%bin% '; see if binary files are turned on. mysql>show master status; +------------------+----------+--------------+------------------+
| File | Position | binlog_do_db | binlog_ignore_db |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 | 308 | | |
+------------------+----------+--------------+------------------+
1 row in Set (0.00 sec)
Note: Do not operate the master server MySQL again after performing this step to prevent the change of the primary server state value
7. Configure the slave from the server:Mysql>change Master tomaster_host= ' 192.168.145.222 ',master_port=3306, #如果端口是默认的, it is also possible not to write this option.
Don't put quotes here .
will report a syntax error. The environment is mysql5612 the error of the newspaper, do not know other
version will not error
master_user= ' Mysync ',master_password= ' q123456 ',master_log_file= ' mysql-bin.000004 ',master_log_pos=308; Be careful not to disconnect, there are no single quotes around 308 digits.
Mysql>start Slave; To start the Copy from Server feature
8. Check the status of the replication function from the server:
mysql> show Slave status\g
*************************** 1. Row ***************************
slave_io_state:waiting for Master to send event
master_host:192.168.2.222//Primary server address
Master_user:mysync//Authorization account name, try to avoid using root
master_port:3306//Database port, some versions do not have this lineconnect_retry:60the name of the primary server binary log file that the master_log_file:mysql-bin.000004//I/O thread is currently reading. read_master_log_pos:600//In the current primary server binary log, the I/O thread has read the location of the synchronous read binary log, greater than or equal to Exec_master_log_posrelay_log_file:ddte-relay-bin.000003//The name of the trunk log file that the SQL thread is currently reading and executingrelay_log_pos:251//In the current trunk log, where the SQL thread has been read and executed. relay_master_log_file:mysql-bin.000004
Slave_io_running:yes//This status must be YesSlave_sql_running:yes//This status must be Yesseconds_behind_master:0 Master-Slave synchronization delay ......
Note: The
slave_io and slave_sql processes must function normally, that is, the Yes state, otherwise it is an error state (e.g., one of the No is an error).
the above operation process, the master and slave server configuration is complete.
9, the master-slave server test:
master server MySQL, build the database, and create a table in this library to insert a piece of data:
10. Note: Our server itself MySQL is open, after modifying the configuration file, to restart the mysql,5.6 will sometimes fail, is because there are other processes MySQL, we service mysqld restart will error. So you need to kill all the MySQL processes. It can be restarted.
MySQL Master-slave replication
