Shulou(Shulou.com)06/01 Report--

# #

MySQL database master-slave synchronization: let other database servers automatically synchronize data on the database server that is being served. Build master-slave synchronization (a) ensure that the master and slave databases have the same data as the slave database (b) configure the master server to enable binlog logging, server_id, authorized users (c) configure the slave server to set up server_ id [master] / etc/ my.cnf [mysqld] server_id=11log-bin=master11binlog-format= "mixed" / specify log format # systemctl start mysqld#mysql > grant replication slave on *. * slaveuser@ "%" identified by "123456" / authorize slaveuser users to log in on all hosts with the permission of replication slaveshow master status / View binlog log [slave] / etc/ my.cnf [mysqld] server_id=12 / do not enable binlog log mysql > change master to > master_host= "192.168.4.11", / indicate the address of the main server > master_user= "slaveuser", / indicate that the slaveuser authorized user is used to log in to the main server > mster_password= "123456", / password of the authorized user > master_log_file= "master11.000001" / indicate the binlog log of the master server now > master_log_pos=154 / offset of master server binlog log # start slave; / start replication # show slave status / check slave status slave_IO_running:yes / IO thread running slave_SQL_running:yes / SQL thread running IO thread: copy the SQL in the master host binlog log file to the local relay-log file SQL thread: execute the SQL statement in the native relay-log file Problem solving when reproducing master data operation: I / O thread status is no-- > not connected to the master server. SQL thread state is no-- > master / var/lib/mysqlmaster.info data out of sync, etc. # ls / var/lib/mysqlmaster.info / connection master server information relay-log.info / relay log message host name-relay-bin.xxxxxx / relay log host name-relay-bin.index relay log index file master Slave synchronization mode one master one slave one master and multiple slaves (master-slave (master)-slave) / 1 is the master of 2 2 is the slave of 1 and master of 3, 3 is the slave master structure of 2 (mutual master) master library configuration file (valid for all slave servers) only allows synchronized library binlog_do_db= library 1, library 2 does not allow synchronized library binlog_ignore_db= library 1, library 2 slave library configuration file only allows synchronized library replicate_do_db= library 1, library 2 does not allow synchronous library replicate_ignore_db= library 1 In order to relieve the pressure on the master server, the separation of read and write is adopted, that is, when the client performs the write operation, the client enters the main server and the client performs the read operation. Enter the slave server implementation method: maxscale software + one master and one slave [master11] [slave12] slave12 is the slave server of master11 [monitor13] # rpm-ivh maxscale.. Modify configuration file / etc/maxscale.cnf (a) specify database server [server1] [server2] (b) specify database server for monitoring (c) specify read and write between those servers (d) define management service (e) port used by read-write separation port=4006 (f) port used by management service port=4009 (g) according to configuration file Add the appropriate authorized user [master11] > grant replication slave replication client on *. * to scalemon@ "%" identified by "123456" to the database server Use scalemon users to monitor read-write separation server > grant select on mysql.* to saclemax@ "%" identified by "123456" Use scalemax users to monitor whether the logged-in user is an authorized user of the database [monitor13] maxscale-- help#maxscale-f / etc/maxsacle.cnf / start the service # netstat-lnptu | grep: 4006maxadmin-uamdin-pmariadb-P4009maxscale > list servers / list the monitored server # killall maxscale / kill process, stop service verification [mysql] # mysql-h292.168.4.13-P4006-uuser-p123456 > select @ @ hostname / check which database server you are currently in

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.