MySQLMasterSlave data synchronization, cluster

Source: Internet
Author: User
I recently learned about MySQL clusters and found that there are many ways to configure MySQL clusters. The following describes how to configure MySQLMasterSlave literally.

I recently learned about the MySQL cluster and found that there are many methods for MySQL clusters. The following describes how to configure the MySQL Master Slave literally.

I recently learned about the MySQL cluster and found that there are many methods for MySQL clusters. The following describes the MySQL Master Slave configuration.

The MySQL Master Slave is literally understood as a relationship between the Master and its subordinates. What can they do? My personal understanding: in the case of high concurrency access to the database, you can allow the SQL statement of the query operation to access the Slave server, insert, modify, delete, and do so on the Master server, this reduces the burden on the server.

Server Load balancer quickly synchronizes data based on the Master node using a binary stream.

I checked a lot of related configurations on the Internet over the past two days, but I couldn't run them. I finally succeeded with my understanding of multiple configurations. I am so happy!

Let me explain how I configured it! Strive to make your configuration successful once by my configuration! I try to detail it as much as possible.

My machine is an XP system, and MySQL5.1.32-community-log is installed. In my VM, It is Ubuntu10.10 and MySQL5.1.49-1ubuntu8. 1-log.

Master: 192.169.105.203 (XP system)
Slave: 192.168.2.133 (Linux)

1. operations on the XP system:
Find the installation directory of MySQL, which is:
D: \ Program Files \ MySQL Server 5.1
Modify the D: \ Program Files \ MySQL Server 5.1 \ my. ini file and find [mysqld] and write it below:

Server-id = 1
Log_bin = d:/mysql/mysql-test-bin.log
Expire_logs_days = 10
Max_binlog_size = 100 M
Binlog_format = mixed
Binlog_do_db = test_db

Note: d:/mysql/mysql-test-bin.log this directory needs to be built by yourself, log_bin means to write the database content in binary mode to the file (Slave will synchronize data based on this file );
Generally, it is better to configure the server-id = 1 number on the Master server to be the same as the number in the last group of IP addresses, but smaller than 32. Here the value is 1.
Binlog_do_db allows the database to be synchronized by Slave. Here I want to write the database test_db for testing. (The database and table structure must also be the same in Slave; otherwise, the database cannot be synchronized ), if multiple databases exist, you can write several more binlog_do_db = ** database names.
Restart MySQL, net stop mysql, and net start mysql

Connect to mysql-uroot-p123456

Mysql> show master status;
+ ----------------------- + ---------- + -------------- + ------------------ +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+ ----------------------- + ---------- + -------------- + ------------------ +
| Mysql-test-bin.000001 | 106 |
+ ----------------------- + ---------- + -------------- + ------------------ +
1 row in set (0.00 sec)

This is the information of the generated binary file. Slave will synchronize the data based on this.

Set Replication Slave Permissions
We must set it on the Master Server so that Slave has the permission to Copy data from the Master Server (officially speaking, Replication Slave Priviledges)
Mysql> grant replication slave on *. * TO 'test _ dbuser' @ '192. 168.2.20.'identified BY '123 ';
In the statement, SLAVE is the permission name, *. * is the access permission to any database, test_dbuser is the user name, 192.168.2.20.is the permission to access this IP, and 1234 is the password.
Mysql> flush privileges; (to make it take effect)

--------------------------
2. Configure Slave in Ubuntu as follows:
I won't talk much about MySQL installation. apt-get install mysql ......
First, describe the path of my MySQL:
In/etc/init. d/mysql, services are started and stopped.
/Var/log/mysql/log, equivalent to the d:/mysql/mysql-test-bin.log location I created in the previous XP System
/Var/lib/mysql/store the database. MySQL configuration file:/etc/mysql/my. cnf, which specifies the data file storage path,
Datadir =/var/lib/mysql
If you create a database named test, the data in the database will be stored in the/var/lib/mysql/test directory.

Slave Server Configuration:
Modify the MySQL configuration file:/etc/mysql/my. cnf
Find [mysqld] and add it below:
Server-id = 2
Replicate-do-db = test_db
Log_bin =/var/log/mysql/mysql-bin.log
Relay_log =/var/log/mysql/mysql-relay-bin.log
Master-connect-retry = 1
Expire_logs_days = 10
Max_binlog_size = 100 M
# Binlog_do_db = test
# Binlog_ignore_db = include_database_name
Binlog_format = mixed

It is described below:
Server-id = 2 is a number. If there are multiple server Load balancer servers, they are directed to the back row in sequence ......
Replicate-do-db = test_db is the database configured to be synchronized to the Master server. Here I configure the test_db database.
Master-connect-retry = 1 is a one-second synchronization, which can be configured according to different needs.

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.