Mysql database read/write splitting Configuration

Environment Simulation

Implement read/write splitting to reduce database load
Master server 10.0.0.12
Slave server slave 10.0.0.66

 

Bytes ------------------------------------------------------------------------------------------------------------------------

Configure the master server: operate on the 10.0.0.12 Server
Create a database
Create database mydemo;

 

1. modify the configuration file

Vi/etc/my. cnf
Add the following code after [mysqld]

Sync_binlog = 1binlog-do-db = mydemo # Set the database name to be synchronized binlog_ignore_db = mysql # databases that do not need to be synchronized. If there are multiple databases that do not need to be synchronized, list them in sequence # binlog_ignore_db = xxxx: wq 2. restart mysql Server service mysqld restart 3. go to the mysql platform and set the user account to be synchronized. create user 'username' @ 'slave Server IP address 'identified by' password 'create user 'eduask '@ '10. 0.0.66 'identified by 'test123' # create a slave server user to synchronize data to the master server 4. grant all on * to the slave user *. * to 'eduask '@ '10. 0.0.% 'identified by 'test123 'grant replication slave on *. * to 'eduask '@ '10. 0.0.66 'identified by 'test123' allow remote login from the server user 5. refresh the authorization table flush privileges6. create table userinfo (uid int primary key auto_increment, uname char (30) not null, upwd char (30) not null) for the database ); insert into userinfo ('uid', 'uname', 'upwd ') values (null, 'aaa', 'test123') 7. exit System \ q exit ------------ perform operations after the server configuration is complete ----------- 8. export the master server database to the slave server/usr/local/web/mysql/bin/mysqldump-uroot-ptest123 Database Name>/home/database name. SQL scp from where to where scp/home/database name. SQL root@10.0.0.66:/home [prompt: if the system prompts you to remotely transfer yes] The system will prompt you to enter the password: test1239. restart the master server service mysqld stop service mysqld start10. enter the mysql platform/usr/local/web/mysql/bin/mysql-uroot-ptest123 11. query the master server-like master show master status. After running, mysql displays a server log status table of the current master. Remember the values of the two fields file: mysql-bin.000006 # This is the binary file position: 519 slave which needs to be synchronized from the server (in 10.0.0.66) 1. go to the mysql platform/usr/local/web/mysql/bin/mysql-uroot-ptest1232. create database mydemo; eixt3. name of the database/usr/local/web/mysql/bin/mysql-uroot-ptest123 imported into the master database