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

Today, I will talk to you about the role of replication in mysql. Many people may not know much about it. In order to let you know more, Xiaobian summarized the following contents for you. I hope you can gain something according to this article.

1. In master mysql, modify/etc/my.cnf and add the following two lines under mysqld:

log-bin=/var/lib/mysql/mysql-bin.log

server-id=1

#binlog-do-db=DB1

#binlog-do-db=DB2 #If you have multiple backups, repeat this option

2. Restart, add slave replication user

GRANT REPLICATION SLAVE ON *.* TO IDENTIFIED BY slave_password;

FLUSH PRIVILEGES;

FLUSH TABLES WITH READ LOCK;

SHOW MASTER STATUS;

+------------------+----------+--------------+------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| mysql-bin.000001 | 890 | | |

+------------------+----------+--------------+------------------+

1 row in set (0.00 sec)

unlock tables;

Write down the file name and the location of the binlog.

4. Working on slave mysql.

Edit the configuration file from the server: /etc/my.cnf

[mysqld]

#sync data

server-id=2 #Note that it cannot be repeated

master-host=192.168.100.10

master-user=slave

master-password=slave_password

master-port=3306

master-connect-retry=60

#replicate-do-db=DB1

#replicate-do-db=

5. Restart slave mysql .

stop slave;

CHANGE MASTER TO MASTER_HOST=192.168.100.13, MASTER_USER=slave, MASTER_PASSWORD=slave_password, MASTER_LOG_FILE=mysql-bin.000001, MASTER_LOG_POS=890;

start slave;

5. Create a database on master mysql to test whether the slave libraries are synchronized. or show slave statusG View

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Two yes's for synchronization.

Additional:

Using show slave statusG on slave servers

Slave_IO_Running, for No,

IO_THREAD is not started. Please start slave io_thread.

Slave_SQL_Running is No

Then copy error, check Last_error field to eliminate error and execute start slave sql_thread

View Slave_IO_State field null//copy not started

Connecting to master//No connection to master

Waiting for master to send event//already connected

Related commands on the primary server:

show master status

show slave hosts

show logs

show binlog events

purge logs to log_name

purge logs before date

reset master(older version flush master)

set sql_log_bin=

Related commands from the server:

slave start

slave stop

SLAVE STOP IO_THREAD //This thread writes the master section log locally

SLAVE start IO_THREAD

SLAVE STOP SQL_THREAD //This thread applies locally written logs to the database

SLAVE start SQL_THREAD

reset slave

SET GLOBAL SQL_SLAVE_SKIP_COUNTER

load data from master

show slave status(SUPER,REPLICATION CLIENT)

CHANGE MASTER TO MASTER_HOST=, MASTER_PORT=,MASTER_USER=, MASTER_PASSWORD= //dynamically change master information

PURGE MASTER [before date] Delete master synchronized logs

After reading the above, do you have any further understanding of what replication does in mysql? If you still want to know more knowledge or related content, please pay attention to the industry information channel, thank you for your support.

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.