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

1. Installation of MySQL master-slave library

1. After entering https://www.mysql.com, click DOWNLOADS to enter the download page.

2. After entering the download page, select MySQL Community (GPL) Downloads.

3. After entering MySQLCommunity (GPL) Downloads, select MySQLCommunity Server.

4. Select Windows as the operating system. At this time, the latest version is 8.0.19. You can directly click Download to download. In order to ensure stability, choose version 5.7.29 for this environment installation. Click Looking for previous GA version? Download the historical version.

5. pay attention to the selection of database version, operating system, and the number of software digits.

6. Click to download directly after selecting the right one.

7. The downloaded software is decompressed directly and placed in a folder that does not contain the Chinese path (create a new my.ini file by yourself).

8. Modify the data in my.ini as follows (set service-id value and log-bin value to prepare for subsequent master / slave construction). Server-id uniquely identifies a database instance. If log-bin is not configured, Empty set will appear after executing show start master.

[mysql]

# set mysql client default character set

Default-character-set=utf8

[mysqld]

Server-id = 1

# enable binary logging

Log-bin=mysql-bin

# set port 3306

Port = 3306

# set the installation directory of mysql

Basedir=D:\ MySQL Server 5.7.29\ mysql-5.7.29-winx64

# set the storage directory of the data in the mysql database

Datadir=D:\ MySQL Server 5.7.29\ mysql-5.7.29-winx64\ data

# maximum number of connections allowed

Max_connections=200

# the character set used by the server defaults to the 8-bit coded latin1 character set

Character-set-server=utf8

# default storage engine to be used when creating new tables

Default-storage-engine=INNODB

9. Add MySQL environment variable configuration (computer right click-> Properties-> Advanced system Settings-> Environment variable) to PATH.

; d:\ MySQL Server 5.7.29\ mysql-5.7.29-winx64\ bin

10, enter the MySQL storage path, in the blank folder, hold down the shift key and then right-click, "Open the command window here" appears, and then enter the CMD window.

11. Enter: mysqld-- install master-- defaults-file= "D:\ MySQL Server 5.7.29\ mysql-5.7.29-winx64\ my.ini" in the corresponding CMD window to install, and Service successfully installed appears, indicating that the service is installed successfully. Note: the red font master is the service name of the main library mysql, and the path part needs to be modified according to their own conditions.

There may also be errors, missing computers and missing MSVCP120.dll. Just go to Microsoft's official website https://www.microsoft.com/zh-CN/download/details.aspx?id=40784 to download Visual C++ Redistributable Packages for Visual Studio 2013 and then re-perform the MySQL installation.

12. If you continue to execute the net start master startup service in the current CMD window, the following indicates that the service has been successfully started. After the installation of the main library is completed, you can use nactive to connect to the corresponding database. Use the user name: root, password: empty, and the port is the port set in my.ini.

13. After connecting to the master library correctly, create a slave library. Copy a master library file and modify the file name to the source folder name + the port number that needs to be set from the library. Note: it can be modified according to individual circumstances, so that it is easy to distinguish between the two database source files.

14. After the slave library file is successfully copied, go to D:\ MySQL Server 5.7.29\ mysql-5.7.29-winx64-3307 to modify the my.ini to the following file. Note that if the service-id is consistent with the master database, it will be reported when starting the master-slave relationship (Fatal error: The slave I _ thread stops because master and slave have equal MySQL server UUIDs) These UUIDs must be different for replication to work.), the port also needs to be modified, otherwise an error will be reported during the installation, and the log-bin value must be set otherwise an error will be reported (Got fatal error 1236 from master when reading data from binary log).

[mysql]

# set mysql client default character set

Default-character-set=utf8

[mysqld]

Server-id = 2

# enable binary logging

Log-bin=mysql-bin

# set port 3307

Port = 3307

# set the installation directory of mysql

Basedir=D:\ MySQL Server 5.7.29\ mysql-5.7.29-winx64-3307

# set the storage directory of the data in the mysql database

Datadir=D:\ MySQL Server 5.7.29\ mysql-5.7.29-winx64-3307\ data

# maximum number of connections allowed

Max_connections=200

# the character set used by the server defaults to the 8-bit coded latin1 character set

Character-set-server=utf8

# default storage engine to be used when creating new tables

Default-storage-engine=INNODB

15. Modify the UUID value in D:\ MySQL Server 5.7.29\ mysql-5.7.29-winx64-3307\ auto.cnf. Because it is copied, if it is not modified and the value of the master database is consistent with that of the master database, an error will be reported when building the master and slave (Fatal error: The slave I thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.).

[auto]

Server-uuid=39e646a2-59f0-11ea-bf0b-14dae9636b20

16. Refer to steps 9, 10, 11 and 12 of installing the master library, install the preparation service and start the slave service.

Mysqld-install slave-defaults-file= "D:\ MySQL Server 5.7.29\ mysql-5.7.29-winx64-3307\ my.ini"

Net start slave

17. After successfully connecting to the master database, use the nactive tool to create a user named sync for master-slave synchronization. The server permissions need to be fully checked.

You can also use the command line to create a sample user statement as follows:

Create user sync

Grant replication slave on *. * to 'sync'@'127.0.0.1'identified by' sync'

Flush privileges

Second, master-slave relationship

1. Execute show master status; in the command line interface of the main library and remember the values corresponding to the File column and the Position column, which will be used later when you associate the master library from the library.

2. Open the command line interface of the slave library and execute

Change master to master_host='127.0.0.1',master_port=3306,master_user='sync',master_password='sync'

Master_log_file=' mysql-bin.000004',master_log_pos= 1924

3. Execute the command start slave; in the slave library to enable master-slave synchronization.

4. Execute show slave status\ G from the library; check the status of salve with newline or show slave status without newline. The status of Slave_IO_Running and Slave_SQL_Running is YES.

5. Then you can add, delete and modify it in the main library. Check the effect in the slave library. It is best to create a user with a query to query from the slave library.

III. Additional information

1. View the server_id command: show variables like 'server_id'

2. The slave library and the master library can be associated many times, and after the execution of stop slave;, the association can be made.

Change master to master_host='127.0.0.1',master_port=3306,master_user='sync',master_password='sync'

Master_log_file=' mysql-bin.000004',master_log_pos= 1924

Pay attention to modifying master_log_file and master_log_pos and other configuration information

Finally, you can execute start salve;.

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.