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

Today, I will talk to you about how to create replication users in MySQL, many people may not know much about it. In order to make you understand better, the editor has summarized the following content for you. I hope you can get something according to this article.

Scenario 1: create replication users only in the main library

1. Make a backup of the master library and copy it to the slave library

/ usr/local/mysql5732/bin/mysqldump-single-transaction-master-data=2-B zlm-S / tmp/mysql3332.sock-p > zlm.sql scp zlm.sql root@10.186.60.68:~

two。 Log in to perform import from the library

Mysql > source zlm.sql

Since you do not use the parameter-- set-gtid-purged=off, the exported statement will be executed with SET @ @ GLOBAL.GTID_PURGED='xxxx:1-xx', and you need to execute reset master on the slave library before importing.

3. The main library creates replication users

Mysql > create user repl1 identified by 'repl1'

4. Configure master-slave replication from the slave library and start

Mysql > change master to master_host='10.186.60.62',master_port=3332,master_user='repl1',master_password='repl1',master_auto_position=1; mysql > start slave

After starting replication, an error of Error 1045 is reported. Here, the password is not wrong, but the replication user is not configured with replication slave permission. After authorizing the repl1 user on the main database (grant replication slave on *. * to repl1;), it is normal to start replication.

After the master-slave replication is normal, the replication user repl1 is also created on the slave library.

The replication user repl1 has not been created from the library, and the master-slave replication has been set up normally. Why? Because the master_user specified in the change master to statement is the replication user on the master database, the slave database connects to the master database through this user to synchronize. When the replication thread is enabled, the statement that creates a replication user on the master database will be played back on the slave database, so there will also be this replication user on the slave database.

Conclusion 1

When building master-slave replication, it is not necessary to create a replication user in the slave library, only in the master library, and the replication user will synchronize to the slave library.

Scenario 2: master and slave libraries create replication users separately (create statement)

1. Make a backup of the master library and copy it to the slave library (gtid_purged=xxxx:1-23)

two。 Perform an import from the library

3. The main library creates replicated users and empowers them

Mysql > create user repl2 identified by 'repl2'; mysql > grant replication slave on *. * to repl2

4. Create a replication user from the library

Because you do not want to generate binlog transactions written by the slave library uuid on the slave library, sql_log_bin=0 is set here so that transactions are not recorded in binlog, because when the database management platform manages the highly available cluster, it is usually not allowed to have GTID transactions on the slave database that do not exist in the master database.

5. Configure master-slave replication from the slave library and start

Mysql > change master to master_host='10.186.60.62',master_port=3332,master_user='repl2',master_password='repl2',master_auto_position=1; mysql > start slave

Since a replication user has been created on the slave library, an error of Error 1396 will be reported when the transaction is played back to the main library.

You can use the create user statement to create a duplicate user to authenticate.

Parse the main library binlog, and the first transaction executed after starting the replication is the create user statement of this 24.

Conclusion 2

After the backup is imported from the library and the replication user is created separately in the master and slave library, the replication will be interrupted when the transaction between the slave library and the creation user is executed.

Scenario 3: master and slave libraries create replication users separately (grant statement)

1. Make a backup of the master library and copy it to the slave library (gtid_purged=xxxx:1-28)

two。 Perform an import from the library

3. The main library creates replication users

Mysql > grant replication slave on *. * repl3 identified by 'repl3'

4. Create a replication user from the library

5. Configure master-slave replication from the slave library and start

Mysql > change master to master_host='10.186.60.62',master_port=3332,master_user='repl2',master_password='repl2',master_auto_position=1; mysql > start slave

No error was reported after starting the replication this time. Why can you create a user with a grant statement, but not a create statement?

Both create and grant statements generate transactions and record them in binlog, but the difference is that the grant statement is an approximately idempotent operation, while the create statement is not.

Parsing the main libraries binlog,29 and 30 are transactions that repeatedly execute grant.

Observe show slave stauts\ G, and play back the two transactions of 29Cool 30 from the library, and repeated execution of them does not affect master-slave replication.

It is important to note, however, that the creation of users through the syntax of grant is prohibited in MySQL 8.0.

Conclusion 3

After importing backups from the library and creating users using grant statements from the master and slave libraries respectively, it does not cause replication interruptions during playback from the library.

Summary

1. According to the above verification results, it is feasible to create replication users in a variety of ways when building master-slave replication, but there are some limitations, such as creating replication users on master and slave instances respectively. Although executing grant statements to create users does not cause replication interruption, it is not a standard MySQL creation user syntax and has been regarded as a syntax error in MySQL 8.0, so it is not recommended to build a master and slave in this way.

two。 How to create replicated users

When the Create statement creates a user

1. After the master library has created the replication user, make a backup, and then configure the master and slave.

two。 After backup, only replication users are created in the master database, and then the master and slave are configured (recommended)

3. If you want to create replication users in the master and slave libraries respectively, you should first set the sql_log_bin=0 at the session level, and then configure the master and slave

When the Grant statement creates a user (MySQL version 5.7 or later)

1. The master library first creates replication users and then backs up, and then configures the master and slave

two。 Create replication users only in the master library, and then configure master and slave (recommended)

3. Master and slave libraries respectively create replication users, and then configure master and slave

After reading the above, do you have any further understanding of how to create replication users in MySQL? If you want to know more knowledge or related content, please follow 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.