Yaya's leader said that he wanted to do database synchronization search and got a tutorial from the Internet. However, he couldn't do it after searching for hundreds of times. He was also drunk. After Google, it seemed that it was okay. Below, Huazi roughly compiled the mysql 5.5 database master-slave synchronization tutorial
192.168.21.169 mysql main server
192.168.21.168 mysql from the server
2. Configure the MySQL main server (192.168.21.169)
mysql -uroot -p #Go to the MySQL console
create database osyunweidb; #Create a database osyunweidb
insert into mysql.user(Host,User,Password) values(‘localhost’,’osyunweiuser’,password(‘123456’)); #Create user osyunweiuser
flush privileges; #Refresh the system authorization table
grant replication slave on *.* to 'osyunweidbbak'@'192.168.21.168' identified by '123456' with grant option; #Create a MySQL master-slave database synchronization user, authorized user osyunweidbbak can only access the database on the main server 192.168.21.169 from the IP of 192.168.21.168, and only has database backup rights
3. Configure the my.ini file of the MySQL main server Location in the mysql root directory
#Edit the configuration file and add the following content in the [mysqld] section
server-id=1 #Set the server id, 1 means the main server. Note: If this line already exists in the original configuration file, there is no need to add it.
log_bin=mysql-bin #Start the MySQ binary logging system. Note: If this line already exists in the original configuration file, there is no need to add it. Pay special attention to what your journal address is...
binlog-do-db=osyunweidb #Name of the database to be synchronized. If there are multiple databases, this parameter can be repeated, with one line for each database
binlog-ignore-db=mysql #Unsynchronized mysql system database
service mysqld restart #Restart MySQL
mysql -u root -p #Go to mysql console
show master status; Check the master server, and the following similar information appears
+------+----+----–+------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------+----+----–+------+
| mysql-bin.000019 | 7131 | osyunweidb | mysql |
+------+----+----–+------+
1 row in set (0.00 sec)
Note: Remember the value of File here: mysql-bin.000019 and the value of Position: 7131, which will be used later.
4. Configure the my.ini file of the MySQL slave server
#Edit the configuration file and add the following content in the [mysqld] section
server-id=2 #There is already a line server-id=1 in the configuration file. Change its value to 2, which means that it is downloaded from the database.
log-bin=mysql-bin #Start the MySQ binary logging system. Note: If this line already exists in the original configuration file, there is no need to add it.Pay special attention to what your journal address is...
replicate-do-db=osyunweidb #Name of the database to be synchronized. If there are multiple databases, this parameter can be repeated, with one line for each database
replicate-ignore-db=mysql #Unsynchronized mysql system database
service mysqld restart #Restart MySQL
Note: After MySQL version 5.1.7, writing master configuration attributes to the my.cnf configuration file is no longer supported. You only need to write the synchronized database and the database to be ignored.
mysql -u root -p #Go to MySQL console
slave stop; #Stop the slave synchronization process
change master to master_host=’192.168.21.169′,master_user=’osyunweidbbak’,master_password=’123456′,master_log_file=’mysql-bin.000019‘ ,master_log_pos=7131; #Execute synchronization statements
Fill in the file name and location according to your actual situation
slave start; #Start slave synchronization process
SHOW SLAVE STATUS\G #Check the slave synchronization information, and the following appears
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.21.169
Master_User: osyunweidbbak
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000019
Read_Master_Log_Pos: 7131
Relay_Log_File: MySQLSlave-relay-bin.000002
Relay_Log_Pos: 253
Relay_Master_Log_File: mysql-bin.000019
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: osyunweidb
Replicate_Ignore_DB: mysql
Replicate_Do_Table:
Replicate_Ignore_Table:
1 row in set (0.00 sec)
Pay attention to check:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
The value of these two parameters above is Yes, which means that the configuration was successful!
5. Test whether the MySQL master-slave server dual-machine hot standby is successful
1. Enter the MySQL main server
mysql -u root -p #Go to the main server MySQL console
use osyunweidb #Enter the database
CREATE TABLE test ( id int not null primary key,name char(20) ); #Create a test table
2. Enter the MySQL slave server
mysql -u root -p #Go to MySQL console
use osyunweidb #Enter the database
show tables; #Looking at the osyunweidb table structure, you will see a newly created table test, indicating that the database synchronization was successful