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

Note:

After the backup of the main library, check the binlog pos tag bit of the file.

The file name is: binlog_pos_innodb record the main library was marked as

Environment description:

Two MySQL5.7.22 servers installed in CentOS7.4: master and slave

Master server:

IP:192.168.80.51

Port:3306

Slave server:

IP:192.168.80.52

Port:3306

Configuration steps:

1. Modify the my.cnf configuration file of master, enable logging and set server-id.

2. Modify the my.cnf configuration file of slave, enable reading binary log and set server-id.

3. Start the connection between slave and master on slave.

4. Check whether the configuration is successful and troubleshooting.

1. Master library my.cnf configuration file, and add the following configuration under "mysqld":

A SERVER:

[client]

User = pmostc

Password = pmostc

Port = 3306

Socket = / tmp/mysql.sock

[mysqld]

User = mysql

Port = 3306

Socket = / tmp/mysql.sock

Basedir = / workspace/app/mysql/

Datadir = / workspace/data/mysql/

Tmpdir = / tmp

Pid-file = / workspace/data/mysql/mysql.pid

Bind-address = 127.0.0.1

Log-bin=mysql-bin

Server-id=1

Log-error=mysqld.log

Replicate-ignore-table=database.table

Log-slave-updates = 1

Description:

Log-bin is configured to turn on binary logging and write the log on the configured path. The log will be recorded in the form of master-bin.000001.

Log-bin-index configures the directory of binary logging files. Each line in this file is the path to the binary log file.

Server-id is the service ID of the MySQL server, which is used to distinguish between other servers in the master-slave configuration.

After the configuration is successful, restart the mysqld service.

Note: if the file name of log-bin is not defined, it will be named after the hostname hostname, and once the hostname is modified, an error will be reported because the binary file cannot be found. So it's best to display the specified file name.

2. From the my.cnf configuration file, slave adds the following configuration under "mysqld":

[client]

User = pmostc

Password = pmostc

Port = 3306

Socket = / tmp/mysql.sock

[mysqld]

User = root

Port = 3306

Socket = / tmp/mysql.sock

Basedir = / workspace/app/mysql/

Datadir = / workspace/data/mysql/

Tmpdir = / tmp

Pid-file = / workspace/data/mysql/mysql.pid

Bind-address = 127.0.0.1

Server_id = 1921688052

Binlog_format=mixed

Relay-log = relay-bin

Relay-log-index = relay-bin.index

Report-host=

Report-port=22988

Log-bin=mysql-bin

Log-error=mysqld.log

Replicate-ignore-table=database.table

Log-slave-updates = 1

Server-id has the same meaning as master, which is used to distinguish between different mysql servers.

Relay-log is used to record received relay binary logs. Relay-log-index is used to hold the received log path index. If the specified file name is not displayed, it is named after the hostname value.

After saving, you can restart the mysqld service.

3. Full backup of the master database

Mysqldump-uroot-p123456 database > database.sql

4. Restore the full backup data from the master database

Mysqldump-uroot-p123456 database

: perform the operation below.

Mysql > stop slave; / / stop slave.

Configuration marked as synchronized

Mysql > CHANGE MASTER TO MASTER_HOST='192.168.80.51'

-> MASTER_USER='repl'

-> MASTER_PASSWORD='123456'

-> MASTER_LOG_FILE='mysql-bin.000001'

-> MASTER_LOG_POS=0

3. 3. Mysql > start slave

4. Check whether the configuration is successful and troubleshooting

Mysql > show slave status\ G

In the printed state, view the values of the following two parameters:

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

If the values of both parameters are yes, it works fine, and you can add a database or a table on the master database to check if it exists on the slave database.

If either of these parameters is not Yes, there is a problem. You can view the database error log file on slave to see the cause of the error.

Slave_IO_State, Slave_IO_Running, and Slave_SQL_Running indicate that slave has not yet started the replication process.

Common causes of errors:

1. Server-id is the same.

2. User rights are not enough. The required permissions include:

REPLICATION SLAVE,RELOAD,CREATE USER,SUPER .

GRANT REPLICATION SLAVE,RELOAD,CREATE USER,SUPER ON. TO {USER} @ {IP_ADDR} WITH GRANT OPTION

Grant replication slave,replication client on. To slave@'192.168.80.52' identified by "123456"

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.