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

This article introduces the relevant knowledge of "mysql5.5.23 master-slave replication configuration method". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Master-slave replication of mysql is a very mature open source architecture, and many IT companies like to use this solution. The advantages of this scheme are obvious: 1. Execute the query on the slave database to reduce the pressure on the master database. 2. Backing up on the slave library can avoid affecting the performance and services of the master library during the backup. 3. When there is a problem with the master database, you can easily switch to the slave database to avoid business stagnation.

The process of configuration is described in detail below:

Main library: 10.4.14.51

Slave Library: 10.4.14.52

Operating system: centos6.4

Server: HP580G7

Install MYSQL on two servers

The installation of MYSQL is compiled by source code, which will have better performance.

# useradd mysql

# passwd mysql

# groupadd mysql

# useradd-g mysql mysql

# mkdir-p / data/local/mysql

# mkdir-p / data/mysql/data

# chown-R mysql:mysql / data/local/mysql

# chmod-R 755 / data/local/mysql

# chown-R mysql:mysql / data/mysql

# chmod-R 755 / data/mysql

# yum-y install cmake bison ncurses ncurses-devel gcc-c++ libstdc++

(you can configure a local YUM source or an external mirror source)

# tar zxvf mysql-5.5.23.tar.gz

# cd mysql-5.5.23

# cmake-DCMAKE_INSTALL_PREFIX=/data/local/mysql-DMYSQL_UNIX_ADDR=/tmp/mysql.sock-DDEFAULT_CHARSET=utf8-DDEFAULT_COLLATION=utf8_general_ci-DWITH_EXTRA_CHARSETS:STRING=utf8,gbk-DWITH_MYISAM_STORAGE_ENGINE=1-DWITH_INNOBASE_STORAGE_ENGINE=1-DWITH_READLINE=1-DENABLED_LOCAL_INFILE=1-DMYSQL_DATADIR=/data/mysql/data

# make & & make install

II. Master-slave replication configuration of MYSQL

1. Set up the main library

Edit / etc/my.cnf

Join under [mysqld]

Server-id = 1

Binlog-ignore-db = mysql

Binlog-ignore-db = test

Binlog-do-db = meihui7

Enter MYSQL

Mysql > grant replication slave on *. * to meihui7@10.4.14.52 identified by '123456'

Query OK, 0 rows affected (0.00 sec)

Then check to see if the creation is successful.

Mysql > select user,host from mysql.user

+-+

| | user | host |

+-+

| | meihui7 | 10.4.14.52 |

| | root | 127.0.0.1 | |

| | root |:: 1 |

| | localhost |

| | root | localhost |

| | localhost.localdomain |

| | root | localhost.localdomain |

+-+

7 rows in set (0.00 sec)

Mysql > flush privileges

Query OK, 0 rows affected (0.00 sec)

Mysql > show master status

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | |

+-+

| | mysql-bin.000015 | 421 |

+-+

1 row in set (0.00 sec)

You need to write down the values of FILE and Position here, which you need to configure on the slave library.

2. Set up the slave library

Edit / etc/my.cnf

Join under [mysqld]

Server-id= 2 # Note: if this sentence is in the configuration file, it can also be enabled and server-id=1 shielded at the same time.

Replicate-do-db = meihui7

Replicate-ignore-db = mysql

Replicate-ignore-db = test

Save exit

Mysql > change master to master_host='10.4.14.51',master_user='meihui7',master_password='123456', master_log_file='mysql-bin.000015',master_log_pos=421

Query OK, 0 rows affected (0.02 sec)

Mysql > start slave

Query OK, 0 rows affected (0.00 sec)

Mysql > show slave status\ G

Note that these two items should be YES

If there is a mistake, such as

Slave_IO_Running: No

Slave_SQL_Running: Yes

Last_IO_Errno: 1593

Last_IO_Error: Fatal error: The slave The slave O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the-- replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it).

This generally means that the slave library is the same as the SERVER-ID on the master library, resulting in an error.

You can modify the MY.CNF file or change it in this way

View server-id

Mysql > show variables like 'server_id'

Manually modify server-id

Mysql > set global server_id=2; # the values here are the same as those set in my.cnf

Then start SLAVE

Mysql > start slave

Query OK, 0 rows affected (0.00 sec)

Mysql > show slave status\ G

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: 10.4.14.51

Master_User: meihui7

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000015

Read_Master_Log_Pos: 421

Relay_Log_File: test2-relay-bin.000019

Relay_Log_Pos: 567

Relay_Master_Log_File: mysql-bin.000015

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

All right, everything's normal.

Sometimes this error is reported when you start SLAVE.

Mysql > start slave

ERROR 1201 (HY000): Could not initialize master info structure; more error messages can be found in the MySQL error log

The reason for this problem is that master-slave replication has been done before!

The solution is:

Run the command stop slave

Continue to run reset slave after successful execution

Then reset master-slave replication, and then start slave

Add: if the starting environment is not a master-slave architecture, and the data has been available for a period of time, lock the host first: flush tables with read lock; is backed up through mysqldump, and then imported on the slave library. And then do the master-slave configuration. Then unlock: unlock tables;show slave

Status\ G

Verify master-slave replication

Build a table on meihui7 on 10.4.14.51

Mysql > create table T1 (PID int,name varchar (20))

Query OK, 0 rows affected (0.04 sec)

Then check the slave database at 10.4.14.52.

Mysql > use meihui7

Database changed

Mysql > show tables

+-+

| | Tables_in_meihui7 |

+-+

| | T1 |

+-+

1 row in set (0.00 sec)

It is found that there is already one, and the synchronization is successful.

Conclusion: the master-slave replication of mysql is very fast, and generally speaking, the change of a small amount of data does not feel the delay. This model is usually suitable for work environments with low latency requirements, such as forums and photo album spaces.

Note:

Since the (binary log) binlog format may be different between different versions of MySQL, the best combination is that the MySQL version of Master is the same as the Slave version or lower, and the Master version must not be higher than the Slave version.

This is the end of the content of "mysql5.5.23 master-slave replication configuration method". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.