1. Replication-Resolved issues
MySQL replication technology has some of the following features:
Read-write separation (read and write separate)
Data distribution (distribution)
Load balancing (Load Balancing)
Backup (Backups)
High-availability and fault-tolerant rows of availability and failover
2. How replication works
As a whole, there are 3 steps to replication:
Master changes the record to binary log (binary log), which is known as the binaries event, and binary logs events. ）
Slave copies the binary log events of master to its trunk logs (relay log)
Slave redo the events in the trunk log and change the data to reflect its own.
The first part of the process is the master record binary log. Master records these changes in two logs before each transaction update data is complete. MySQL writes the transaction serially to the binary log, even if the statements in the transaction are cross-executed. After the event is written to the binary log, master notifies the storage engine to commit the transaction.
The next step is to slave copy the binary log of master to its own trunk logs. First, slave starts a worker thread--i/o thread. The I/O thread opens a normal connection on master and then starts Binlog dump process. Binlog dump process reads the event from the binary log of master, and if it has been followed by master, it sleeps and waits for master to produce a new event. The I/O thread writes these events to the relay log.
The SQL slave thread (SQL slave thread) handles the last step of the process. The SQL thread reads events from the log and replays the events in them to update the slave data so that it is consistent with the data in master. As long as the thread is consistent with the I/O thread, the trunk log is typically located in the OS cache, so the overhead of the trunk log is minimal.
In addition, there is a worker thread in master: As with other MySQL connections, slave opening a connection in master will also cause master to start a thread. The replication process has a very important limitation-replication is serialized on slave, meaning that parallel update operations on Master cannot operate concurrently on slave.
The figure is as follows:
Configuring the primary server
Linux under MY.CNF win for My.ini
#show variables like ' log_bin '; Value = On only if it is Linux, it is recommended to add the following numeric parameters after [mysqld] # Open Bin-loglog-bin = mysql-bin# master server number = 1server-id = # The database to be recorded binlog-do-db = t EST Add user Rights mysql> grant replication Slave on * * to ' backup ' @ ' 192.168.104.% ' identified by ' 123456 '; the last sentence is a user is added, the user account is a BA Ckup, with a password of 123456, has access to all databases from the database backup permissions do not understand that Grant can go under Baidu, very easy. The main library builds the table from the library table consistent with the master and slave running the following instruction Mysql>reset master resets the Bin-log log
Configuration from Server Server-id = 2 #多台从服务器的时候保证从服务器的ID值的唯一 # Select the database you want to back up and filter the database that is not backed up replicate-do-db=testreplicate-ignore-db= mysqlreplicate-ignore-db=information_schemareplicate-ignore-db=performance_schema# no longer has a master_* configuration in the configuration file after 5.1, It is necessary to run the following information to be feasible mysql>stop slave;mysql>Change Master to master_host= ' 192.168.104.9 ', master_user= ' admin ', master_password= ' 123456 '; #master_host为主库的地址, User and password are the accounts and passwords that we grant to the main database at the top.mysql>Show Master Status\g; host display file: Log storage Position: Location binlog_do_db: Database to log binlog_ignore_db: Database not logged
Mysql>show slave status\g;
Ensure that the master_log_file is consistent with FileRead_Master_log_pos andposition Consistentinconsistencies can also be run with the following values to changeChange Master to =master_log_file=File,Read_master_log_pos=Positionslave_io_running and slave_sql_running both show Yes table synchronization succeeded
MySQL master-slave copy backup
