Shulou(Shulou.com)05/31 Report--

This article mainly explains "the usage of MYSQL master replication under Linux". The content of the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "the usage of MYSQL master replication under Linux".

Why, there is a master copy of mysql. Because in some highly available environments, the master and slave of mysql can not meet some practical needs in reality. For example, some websites with heavy traffic have bottlenecks in database access, and two or more mysql servers are used when load balancing is needed, and the database data of these mysql servers must be consistent, then master replication will be used.

In fact, there is only one master working in the mysql master-slave architecture, and the slave is equivalent to a backup machine, which ensures the data security of the master database by backing up the data on the master database through log monitoring. In this architecture, if the slave data is changed, the master data will not use any changes. Because the mysql master-slave architecture is mainly mysql to monitor the log changes of the mysql master to achieve synchronization, on the contrary, the master does not monitor the slave log changes in this architecture. Therefore, the mysql changes from the data, and there is no change in the Lord.

From the above description, we can see that if you want to achieve master-master replication, it is nothing more than letting the mysql master implement to monitor the slave log changes on the mysql master-slave architecture, so that the two machines can synchronize with each other. (the master-slave architecture is preceded by a blog post http://duyunlong.blog.51cto.com/1054716/1102237)

Lab environment: two servers:

Hostname: HA1,HA2 (hehe, this hostname is the acronym High availability, meaning highly available)

Ip:192.168.1.231

192.168.1.232

Host system: centos6.4

Mysql version 5.5.22

First, take a look at the mysql configuration file for HA1 (192.168.1.231)

Vim / etc/my.cnf# Example MySQL config file for very large systems.## This is for a large system with memory of 1G-2G where the system runs mainly# MySQL.## MySQL programs look for option files in a set of# locations which depend on the deployment platform.# You can copy this option file to one of those# locations. For information about these locations, see:# http://dev.mysql.com/doc/mysql/en/option-files.html## In this file, you can use all long options that a program supports.# If you want to know which options a program supports Run the program# with the "- help" option.# The following options will be passed to all MySQL clients [client] # password = your_passwordport = 3306socket = / usr/local/mysql/tmp/mysql.sock# Here follows entries for some specific programs# The MySQL server [mysqld] port = 3306socket = / usr/local/mysql/tmp/mysql.sockskip-external-lockingkey_buffer_size = 384Mmax_allowed_packet = 1Mtable_open_cache = 512sort_buffer_size = 2Mread_buffer_size = 2Mread_rnd_buffer_size = 8Mmyisam_sort_buffer_size = 64Mthread_cache_size = 8query_cache_size = 32M# Try number of CPU's*2 for thread_concurrencythread_concurrency = Don't listen on a TCP/IP port at all. This can be a security enhancement # if all processes that need to connect to mysqld run on the same host.# All interaction with mysqld must be made via Unix sockets or named pipes.# Note that using this option without enabling named pipes on Windows# (via the "enable-named-pipe" option) will render mysqld uselessful colors skiphands networking # Replication Master Server (default) # binary logging is required for replicationlog-bin=mysql-binlog-slave-updates# required unique id between 1 and 2 ^ 32-defaults to 1 if master-host is not set# but will not function as a master if omittedserver-id = 1

There are three areas to pay special attention to in this configuration file:

Log-bin=mysql-bin: this option is basically on by default. If it is not turned on, you can open it manually.

Log-slave-updates: this option is particularly important in order to enable slave to also act as master, and to better serve the environment of Mmurm + s, ensuring that slave hanging on any master will receive write messages from another master. Of course, not limited to this architecture, cascading replication architecture also needs the support of log-slave-updates.

Server-id = 1: this ID is the server ID. If configured, conflicts will occur and cannot be copied.

Then take a look at the mysql configuration file of HA2 (192.168.1.232)

Vim / etc/my.cnf# Example MySQL config file for very large systems.## This is for a large system with memory of 1G-2G where the system runs mainly# MySQL.## MySQL programs look for option files in a set of# locations which depend on the deployment platform.# You can copy this option file to one of those# locations. For information about these locations, see:# http://dev.mysql.com/doc/mysql/en/option-files.html## In this file, you can use all long options that a program supports.# If you want to know which options a program supports Run the program# with the "- help" option.# The following options will be passed to all MySQL clients [client] # password = your_passwordport = 3306socket = / usr/local/mysql/tmp/mysql.sock# Here follows entries for some specific programs# The MySQL server [mysqld] port = 3306socket = / usr/local/mysql/tmp/mysql.sockskip-external-lockingkey_buffer_size = 384Mmax_allowed_packet = 1Mtable_open_cache = 512sort_buffer_size = 2Mread_buffer_size = 2Mread_rnd_buffer_size = 8Mmyisam_sort_buffer_size = 64Mthread_cache_size = 8query_cache_size = 32M# Try number of CPU's*2 for thread_concurrencythread_concurrency = Don't listen on a TCP/IP port at all. This can be a security enhancement # if all processes that need to connect to mysqld run on the same host.# All interaction with mysqld must be made via Unix sockets or named pipes.# Note that using this option without enabling named pipes on Windows# (via the "enable-named-pipe" option) will render mysqld uselessfolk thanks to skiphouse networking # Replication Master Server (default) # binary logging is required for replicationlog-bin=mysql-binlog-slave-updates# required unique id between 1 and 2 ^ 32-defaults to 1 if master-host is not set# but will not function as a master if omittedserver -id = 1 percent Replication Slave (comment out master section to use this)

In HA2's mysql configuration file, except for server-id, everything else is almost exactly the same. After the configuration file is written, we start the mysql server on both servers.

First, log in to the mysql of HA2 (192.168.1.232) and check the master status

Mysql > show master status +-+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +-- -+-+ | mysql-bin.000016 | 615 | +-+ 1 row in set (0.00 sec) mysql >

Then, log in to the msyql of HA1 (192.168.1.231), configure HA2 as your own master, and create a replicable account in the mysql of the two machines before doing this:

Mysql > grant all on *. * to duyunlong@'192.168.1.%' identified by '123456 makes query OK, 0 rows affected (0.01 sec) mysql > change master to master_host='192.168.1.232',master_user='duyunlong',master_password='123456',master_log_file='mysql-bin.000016',master_log_pos=615

As above, check HA1 (192.168.1.231) master, then log in to HA2 (192.168.1.232), configure HA1 (192.168.1.231) as your own master, and then start slave in the mysql of each machine.

HA1 status after startup

Mysql > show slave status\ G * * 1. Row * * Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.232 Master_User: duyunlong Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000016 Read_Master_Log_Pos: 615 Relay_Log_File: HA1-relay-bin.000002 Relay_Log_Pos: 346 Relay_Master_Log_File: mysql-bin.000016 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 615 Relay_Log_Space: 500 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_ Key: Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 101 row in set (0.00 sec) ERROR:No query specified

You can see Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Then look at the status of HA2:

Mysql > show slave status\ G * * 1. Row * * Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.231 Master_User: duyunlong Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000018 Read_Master_Log_Pos: 552 Relay_Log_File: HA2-relay-bin.000002 Relay_Log_Pos: 441 Relay_Master_Log_File: mysql-bin.000018 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 552 Relay_Log_Space: 595 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_ Key: Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 11 row in set (0.00 sec) ERROR:No query specified

You can see Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Next, we want to test whether it is ready for master and master replication. First, log in to the mysql of HA1 (192.168.1.231) and set up a database. Of course, before testing, let's take a look at what data is in the mysql of the two servers.

First take a look at HA1 (192.168.1.231)

[root@HA1] # mysql mysql-uduyunlong-p123456-h292.168.1.231-e'show databases '+-+ | Database | +-+ | information_schema | | mysql | | performance_schema | | test | | wanghaipeng | +-+ [root@HA1 ~] #

Take another look at HA2 (192.168.1.232)

[root@HA2] # mysql mysql-uduyunlong-p123456-h292.168.1.232-e 'show databases '+-+ | Database | +-+ | information_schema | | mysql | | performance_schema | | test | | wanghaipeng | +-+ [root@HA2 ~] #

As you can see, the data in the mysql on the two servers is the same. Next, set up a database "a" in HA, and then look at the result.

[root@HA1] # mysql mysql-uduyunlong-p123456-h292.168.1.231-e'create database a show databases'[root@HA1] # mysql mysql-uduyunlong-p123456-h292.168.1.231-e'show databases '+-+ | Database | +-+ | information_schema | | a | mysql | | performance_schema | | test | | wanghaipeng | +-+ [root@HA1 ~] #

Then see if HA2 (192.168.1.232) will copy the newly created database "a".

[root@HA2] # mysql mysql-uduyunlong-p123456-h292.168.1.232-e 'show databases '+-+ | Database | +-+ | information_schema | | a | mysql | | performance_schema | | test | | wanghaipeng | +-+

As you can see, the database "a" has been successfully replicated, and in turn, we set up a database "b" on HA2 (192.168.1.232) to see if HA1 can also copy it.

[root@HA2] # mysql mysql-uduyunlong-p123456-h292.168.1.232-e 'create database b show databases' [root@HA2] # mysql mysql-uduyunlong-p123456-h292.168.1.232-e'show databases '+-+ | Database | +-+ | information_schema | | a | b | | mysql | | performance_schema | | test | | wanghaipeng | +-+ [root@HA2 ~] #

Then log in to HA1 (192.168.1.231) to see if the replication is successful

[root@HA1] # mysql mysql-uduyunlong-p123456-h292.168.1.231-e'show databases '+-+ | Database | +-+ | information_schema | | a | b | | mysql | | performance_schema | | test | | wanghaipeng | +-+ [root@HA1 ~] #

In HA1 (192.168.1.231), you can see that the database "b" has been replicated.

So at this point, the master replication architecture has been successful!

Thank you for your reading, the above is the content of "the usage of MYSQL master copy under Linux". After the study of this article, I believe you have a deeper understanding of the usage of MYSQL master copy under Linux, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.