Mysql database read/write splitting Data Synchronization

Mysql database read/write splitting Data Synchronization I used two xp (one master, one slave) systems to test successfully, and I also did a test on linux systems, however, I think the problem I encountered is the same. In xp, the problem can be successful. In linux, the problem should be successful. I will test it later and update the result! PS: after testing, linux can be synchronized successfully. The master server is xp and the slave server is centos. For example, machine A, machine 192.168.0.2, machine B, and machine 192.168.0.3 can be pinged. for mutual access, configure the master server first and configure A synchronization account SQL code 1 GRANT FILE ON *. * TO 'backup '@' 192. 168.0.3 'identified BY '20170101'; 2 grant replication slave on *. * TO 'backup '@' 193. 168.0.3 'identified BY '123'; the account name is backup, and the password is 1234, the ip address is the ip address of the slave server. At this time, we add a synchronization database SQL code 1 create database test 2use test 3 create table mytest (username varchar (20), password varchar (20) on the server )) open my. ini input the following content in [mysqld] Log-bin = c: \ master. log // log file address. in linux, do not add this item. After I add it, I do not know why the error is reported. mysqld cannot be started, I just want to annotate it, and it does not affect other binlog-do-db = test // databases to be synchronized, the difference here is that the commands for synchronizing data from the master server to the database are different from those on the client. The main server is binlog-do-db, the slave server uses replicate-do-db # replicate-ignore-db = mysql. The server_id of the database that is not synchronized is 1 // The number cannot be the same as the slave server id and then restarts. mysql service, you can enter SQL code 1 show master status to view the relevant information. This command is important and will be used later When configuring the slave server. The configuration slave server is the same as the preceding steps, first, create a database that is identical to the master server, and then configure some information about the master server. Note the following, most of the tutorials I have read on the Internet are the Ini code 1server-id = 2 2master-host = 192.168.0.2 3master-user = backup # synchronize the user account 4master-password = 1234 5master-port = 3306 6master-connect-retry = 60 # preset Retry Interval 60 seconds 7replicate-do-db = test # Tell slave to only update the backup database and put the above Code into my. ini, but when I tried it, after adding this code, the mysql service could not start up. After reading the log file, I found the prompt: 111121 21:11:56 [ERROR] wampmysqld: unknown variable 'master-host = 192.168.0.2 'was later found online. Mysql versions earlier than 5.1.7 do not support "master-ho ". St "is similar to the parameter. Therefore, you need to use this method to configure and use the mysql tool SQL code 1 mysql-uroot-p 2 3 change master to master_host = '100. 168.0.2 ', master_user = 'backup', master_password = '000000', master_log_file = 'mysql-bin.000010', master_log_pos = 1234. Pay special attention to the following, I think I have not configured it successfully in linux. This may be the reason. The last two values of master_log_file = master_log_pos = are, run the show master status Command on the master server. In the result, File is the value of master_log_file and Position is the value of master_log_pos. The synchronization fails because of an error. Then open my. the INI file is on the mysqld node, add the Ini code 1replicate-ignore-db = mysql // the database to which synchronization is prohibited 2replicate-do-db = test // the database to be synchronized 3replicate-ignore-db = information_schema // the database to which synchronization is prohibited is not required, however, after the database to be synchronized is configured properly, restart the mysql service. At this time, the client is configured and we use the insert into Statement on the master server, insert two records and check the results in the select query from the server. Finally, you can use the show slave status command in the mysql command line tool to view the related status Slave_IO_State: waiting for master to send event when you use show slave status to find the first row, the status is the state above, it indicates that it is successful, and other statuses seem to be unable to be synchronized