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

Master-slave replication of mysql is mainly realized through binlog logs.

Main steps:

1. Master authorizes slave library connection (operation on master)

Grant replication slave on. To 'backup' (user name) @' 192.168.1.131 (slave ip) 'identified by' backup' (password)

2. Operation on master: show master status; records the log file name on master and the two values of position need to be used later.

3. Operation on slave: let slave connect to master and start redoing the events in the master binary log. The value of master_log_file is the name of the log file above; master_log_pos is the value of position

Change master to master_host='192.168.1.131 (main library ip)', master_user='backup',master_password='backup',master_log_file='ZHAOPAN-PC-bin.000004 (log file name)', master_log_pos=120 (value of position)

4 View the slave library on slave: show slave status\ G; check the configuration information of step 3, and check whether the io process is started. If it is not started, execute it.

Start slave

5 configure read-only on slave and configure read_only=1 in mysqld in configuration file / etc/my.cnf

Note: read_only=1 read-only mode can limit the data modification operations of ordinary users, but does not limit the data modification operations of users with super privileges, such as Super Admin root users.

If you want to ensure that super users can't write either, you need to execute the command "flush tables with read lock;" to lock all tables.

In this way, a user with super permission is used to log in to the database, and when you want to change the data, you will also be prompted that the table is locked and cannot be modified.

Our general practice is to assign a normal user to the slave library.

After setting the slave database read-only=1 to read-only, execute GRANT USAGE ON *. * TO 'user01'@'localhost' IDENTIFIED BY'123456'WITH GRANT OPTION; in master (to distinguish the above method of assigning replication permissions to slave libraries. Here, usage permissions are assigned, followed by with grant option)

Create a normal user

Then log in to the slave library with an ordinary user, and the operation will report an error. After switching to root users, you can still add, delete, modify and check.

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.