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

The following content mainly brings you how to achieve MySql master-slave replication under the Windows system. Unlike books, the knowledge mentioned is summed up by professional and technical personnel in the process of contact with users, and has a certain experience sharing value. I hope to bring help to the majority of readers.

The command to start and shut down mysql. Note: this command is executed in the cmd window

MySql start stop command

Net start mysql

Net stop mysql

Modify the host permissions of the root user in the Mysql.user table to%, otherwise you cannot use the root user to connect remotely

Log in to MySql to execute:

SHOW VARIABLES LIKE'% datadir%'; View my.ini path

Find the configuration file my.ini and add the following code

[mysqld]

Server-id = 1

Binlog-do-db=test # databases to be synchronized

# binlog-ignore-db=mysql # for databases that are out of sync, if binlog-do-db is specified, it should be unnecessary to specify

Log-bin=mysql-bin # name of the binary journal file to be generated

Expire_logs_days = 10 # number of days that binary log files are deleted

Max_binlog_size=100M # maximum size of binary log files

# bind_address=192.168.1.188 specifies the access address

Character-set-server=utf8 # is only available in version 5.5 or above

Save the changed configuration file

Then execute the following sql statement to check whether the modification is successful

Whether the SHOW VARIABLES LIKE'% log_bin%'; query is valid

The value of log_bin is on, which means the modification is successful.

Show variables like 'server_id'

If server_id' does not exist for execution, create SET GLOBAL server_id=1

Next, set up a backup account on master (main library). The account created here is repl%, which means that repl users at any address can log in to master.

GRANT REPLICATION SLAVE ON *. * TO 'repl' @'% 'IDENTIFIED BY' Abcd@1234'

Connect the master library command mysql-h IP address-uroot-p from the library machine

GRANT ALL PRIVILEGES ON *. * TO 'repl'@'%' tests that the slave library is connected to the master library. If the slave library cannot be connected, it is authorized to solve the problem.

Restart the CVM to query the master host information

Show master status; records the information here, and then configure whether the slave library will use the

Second, slave library configuration

Copy the library to be copied by data in the master library to the data of the slave library, and keep the data all the time. Here we use test.

Then edit the my.ini file from the database

[mysqld]

Server-id = 2

Log-bin = mysql-bin

Replicate-do-db=test

Character-set-server=utf8 # is only available in version 5.5 or above

Ps: check whether the configuration file already exists. If so, server-id needs to be commented out.

Show variables like 'server_id'

If server_id' does not exist for execution, create SET GLOBAL server_id=2

Stop the slave service

Stop slave

Set the account number and password of the master database to log in when connecting to the slave library, and then start slave (ps: it's best to re-check the master status in case the information changes show master status;)

CHANGE MASTER TO MASTER_HOST = '192.168.1.188', MASTER_USER = 'repl'

, MASTER_PASSWORD = 'Abcd@1234'

, MASTER_LOG_FILE = 'binlog.000008'

, MASTER_LOG_POS = 154,

MASTER_HOST represents the IP address of the primary server that implements replication

MASTER_USER represents the user who logs in to the remote master server to implement replication

MASTER_PASSWORD represents the user password that logs in to the remote master server to implement replication

(the master_log_file and master_log_pos here correspond to the parameters just noted by show master status. )

MASTER_LOG_FILE represents the binlog log file that is replicated

MASTER_LOG_POS indicates the offset of the replicated binlog log file

Turn on slave

Start slave

Show slave status

It is normal to see if these two items are YES,yes.

Slave_IO_Running: Yes or Connecting to master

Slave_SQL_Running: Yes

Normal means that the configuration is successful, and then the test is performed.

Mysql > create table tianyc_02 (b int)

Query OK, 0 rows affected (0.16 sec)

Mysql > insert into tianyc_02 values (2013)

Query OK, 1 row affected (0.13 sec)

-from the library

Mysql > show tables

+-+

| | Tables_in_test |

+-+

| | tianyc_01 |

| | tianyc_02 |

+-+

2 rows in set (0.00 sec)

Mysql > select * from tianyc_02

+-+

| | b |

+-+

| | 2013 |

+-+

1 row in set (0.00 sec)

Similarly, the second and third slave nodes can be built.

For the above about how to achieve MySql master-slave replication under the Windows system, if you have more information, you can continue to pay attention to the innovation of our industry. If you need professional answers, you can contact the pre-sale and after-sale ones on the official website. I hope this article can bring you some knowledge updates.

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.