Master-slave replication for MySQL ---- mysql-mm

Source: Internet
Author: User
1) Slave starts the IO process to connect to the Master, and requests the log content from the specified location (or from the beginning of the log) after the Master receives the request

1) Slave starts the I/O Process to connect to the Master, and requests the log content from the specified location (or from the beginning of the log) after the Master receives the request

Principles:
AB replication is mainly completed through two slave processes (SQL and I/O processes) and the I/O processes of the Master.
The replication process is mainly because Slave obtains the log from the Master and then executes the operations recorded in the log in full order on itself.
Trilogy of the replication process:
1) Slave starts the I/O Process to connect to the Master, and requests the log content after the specified location of the specified log file (or from the beginning of the log)
2) After receiving the request, the Master will return the location and log information of the bin-log file on the Master end to the Slave through the IO process responsible for replication.
3) after the Server Load balancer receives the information, it will add the received log content to the end of the relay-log file at the Server Load balancer end in sequence, and add the read to the Master end.
The file name and location of bin-log are recorded in the master-info file, so that the next read can clearly tell the Master "I need
The location where a bin-log starts to return the log Content, please send it to me"
After the Slave SQL process detects the newly added content in relay-log, it will immediately parse the relay-log Content to become the actual execution time on the Master end.
And the executable content in

Environment Description: It is recommended that the mysql versions of the two machines be identical.
A: 211.100.97.246 Linux x86_64 mysql5.1.56
B: 211.100.97.250 Linux x86_64 mysql5.1.56

Start the mysql process
Both A and B Start the mysql process.


Modify Security Level
Disable selinux. iptables allows the mysql port connection between two machines.
You can set selinux = disabled in/etc/sysconfig/selinux.
Add iptables-a input-s SourceIP-p tcp -- dport 3306-j ACCEPT
After modifying the test Port:
A: telnet B _ips 3306
B: telnet A_IP 3306

Create an account
A: useradd repl1
B: useradd repl2
View account information after adding
A: id repl1
B: id repl2


A: mysql configuration file
User = mysql
Log-bin = mysql-bin
Server-id = 1
Binlog-do-db = test
Binlog-ignore-db = mysql
Replicate-do-db = test
Replicate-ignore-db = mysql
Log-slave-updates
Slave-skip-errors = all
Sync_binlog = 1
Auto_increment_increment = 2
Auto_increment_offset = 1


B: mysql configuration file
User = mysql
Log-bin = mysql-bin
Server-id = 2
Binlog-do-db = test
Binlog-ignore-db = mysql
Replicate-do-db = test
Replicate-ignore-db = mysql
Log-slave-updates
Slave-skip-errors = all
Sync_binlog = 1
Auto_increment_increment = 2
Auto_increment_offset = 1

Note:
Server-id indicates the serial number of the local machine. If it is 1, it generally indicates the master
Binlog-do-db indicates the database to be backed up. If you want to back up multiple databases, add multiple records.
Replicate-do-db indicates the database to be synchronized
Log-bin indicates that the binlog function is enabled. Only when this option is enabled can the log information on the mater be written to the Slave relay-log through the I/O process.
Auto_increment_increment defines the step of the next AUTO_INCREMENT
Auto_increment_offset defines the starting value of AUTO_INCREMENT.


Authorize the user [Grant at least the FILE, SELECT, and replication slave permissions]
A: Allow B to synchronize data with A through the repl2 account
Mysql> grant replication client on *. * to 'repl2' @ 'B _ ip' identified by 'passwd ';
Mysql> flush privileges;
Check the authorization information:
Mysql> select * from mysql. user where host = 'repl1' @ 'B _ ip' \ G;
* *************************** 1. row ***************************
Host: 211.100.97.250
User: repl2
Password: * 6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Reload_priv: Y
Shutdown_priv: Y
Process_priv: Y
File_priv: Y
Grant_priv: Y
References_priv: Y
Index_priv: Y
Alter_priv: Y
Show_db_priv: Y
Super_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Execute_priv: Y
Repl_slave_priv: Y
Repl_client_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
Create_user_priv: Y
Event_priv: Y
Trigger_priv: Y
Ssl_type:
Ssl_cipher:
X509_issuer:
X509_subject:
Max_questions: 0
Max_updates: 0
Max_connections: 0
Max_user_connections: 0
1 row in set (0.00 sec)

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.