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

The benefits of using mysql master-slave replication are:

1. Using the architecture of master-slave server, the stability can be improved. If the master server fails, we can use the slave server to provide the service.

2. Processing users' requests separately on the master and slave servers can improve the efficiency of data processing.

3. Copy the data from the master server to the slave server to protect the data from unexpected loss.

Environment description:

The new enterprise should build a mysql database with master-slave replication architecture.

Master server (mysql-master): IP address: 192.168.48.128 MySQL has been installed, no user data.

Slave server (mysql-slave): IP address: 192.168.48.130 MySQL has been installed, no user data.

Both master and slave servers can provide services normally.

Configure the primary server (master)

1. Edit the database configuration file my.cnf or my.ini (windows), usually in the / etc/ directory.

Add the following code under [mysqld]:

Log-bin=mysql-bin

Server-id=1

Innodb_flush_log_at_trx_commit=1

Sync_binlog=1

Binlog-do-db=wordpress

Binlog_ignore_db=mysql

Description:

The 1 in server-id=1 / / can be defined as long as it is unique.

Binlog-do-db=wordpress / / means that only wordpress is backed up.

Binlog_ignore_db=mysql / / ignores backup mysql.

Without binlog-do-db and binlog_ignore_db, that means backing up all databases.

2. Then restart MySQL:#service mysqld restart

3. Log in to mysql, add a backup account in mysql, and authorize it to the slave server.

[root@localhost~] # mysql-u root-p 123456 Login to mysql

Mysql > grant replication slave on*.* to 'backup'@'192.168.48.130' identifiedby' backup'

Create a backup user and authorize it to use 192.168.48.130.

4. Query the status of the master database and write down the values of FILE and Position, which will be used later when configuring the slave server.

Mysql > show masterstatus; Please make a note of the information displayed. The configuration will be used from the server.

+-+-

+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+-+-

+ | mysql-bin.000001 | 253 | dbispconfig | mysql |

+-+

1rowinset (0.00sec)

Operate on the slave server:

1) make sure there are log-bin=mysql-bin and server-id=1 parameters in / etc/my.cnf, and change server-id=1 to server-id=10. After modification, it is as follows:

[mysqld]

Log-bin=mysql-bin / / start binaries server-id=10 / / server ID

2) restart the mysql service.

[root@localhost~] # mysqladmin-p123456shutdown

[root@localhost~] # mysqld_safe--user=mysql&

3) Log in to mysql and execute the following statement

[root@localhost~] # mysql-uroot-p123456

Mysql > changemastertomaster_host='192.168.48.128',master_user='backup',master_password='backup',master_log_file='mysql-bin.000003',master_log_pos=401

4) start slave synchronization.

Mysql > start slave

5) check the master-slave synchronization. If you see that both Slave_IO_Running and Slave_SQL_Running are Yes, the master-slave replication connection is normal.

Mysql > show slave status\ G

Verify that the configuration is normal and that the mysql master and slave can replicate properly.

Create a new library on the main database and write a table and some data in the library.

[root@localhost~] # mysql-u root-p 123456

Mysql > create database mysqltest

Mysql > use mysqltest

Mysql > create table user (idint (5), namechar (10))

Mysql > insert into user values (00001)

Verify that it is copied to the data properly from the database.

[root@localhost~] # mysql-u root-p 123456

Mysql > show databases

Mysql > select * from mysqltest.user

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.