1, MySql installation
-
Machine preparation: 10.117.201.80 (slave server, 8 cores), 10.117.201.81 (master server, 16 cores)
-
Download mysql installation package: mysql-5.7.30-linux-glibc2.12-x86_64.tar.gz
-
Upload to two linux servers
[root@mon mysql]# pwd /usr/local/mysql [root@mon mysql]# ls mysql-5.7.30-linux-glibc2.12-x86_64.tar.gz
-
decompression
[root@mon mysql]# tar -xzvf mysql-5.7.30-linux-glibc2.12-x86_64.tar.gz -C /usr/local/mysql/
Unzip it to the specified path, and change the name to mysql5.0 through mv command seven point three zero
[root@mon mysql5.7.30]# pwd /usr/local/mysql/mysql5.7.30 [root@mon mysql5.7.30]# ls bin data docs include lib LICENSE man README share support-files
-
Create user group
[root@mon mysql5.7.30] groupadd mysql [root@mon mysql5.7.30] useradd -r -g mysql mysql
-
Change the owner and group of the installation directory to mysql
[root@mon mysql5.7.30] chown -R mysql.mysql /usr/local/mysql/mysql5.7.30
-
Create a data folder to store data such as database tables
[root@mon mysql5.7.30] mkdir data
-
initialization
[root@mon mysql5.7.30] /usr/local/mysql/mysql5.7.30/bin/mysqld --user=mysql --basedir=/usr/local/mysql/mysql5.7.30/ --datadir=/usr/local/mysql/mysql5.7.30/data --initialize
-
Edit profile
The mysql configuration file in linux is / etc / my CNF. Write the following in the configuration file
[mysqld] #This is to start binlog log log-bin=mysql-bin #This means that the master server and slave server must not be the same server-id=1 datadir=/usr/local/mysql/mysql5.7.30/data basedir=/usr/local/mysql/mysql5.7.30 socket=/tmp/mysql.sock user=mysql port=3306 character-set-server=utf8 #Set the maximum number of mysql connections max_connections = 1000 default-time-zone = '+08:00' lower_case_table_names=1 # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 # Cancel password verification # skip-grant-tables [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid
-
Add mysql to the service
[root@mon mysql5.7.30] cp /usr/local/mysql/mysql5.7.30/support-files/mysql.server /etc/init.d/mysql [root@mon mysql5.7.30] chmod +x /etc/init.d/mysql
-
Set startup
[root@mon mysql5.7.30] chkconfig mysql on
-
service mysql start
[root@mon mysql5.7.30] service mysql start
Add: service mysql stop stop stop mysql service, service mysql restart restart mysql service
-
Log in to mysql
Add the configuration path to the environment variable and edit the file
[root@mon mysql5.7.30] vi /etc/profile export PATH=$SPLUNK_HOME/bin:$PATH:/usr/local/mysql/mysql5.7.30/bin Mainly bin Add your path
Then check it to take effect
[root@mon mysql5.7.30] source /etc/profile
Because in the configuration file, / etc / my CNF skips password login, so you can log in without password
[root@mon mysql5.7.30] mysql -uroot -p Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 25 Server version: 5.7.30-log MySQL Community Server (GPL) Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
2, Master slave configuration
-
The above steps are for installing mysql. Then, the next step is the configuration of the master-slave server. For the configuration of the master-slave server, I copy a copy of / etc / my CNF file to the corresponding path on slave machine 80.
[root@mon mysql5.7.30] scp -r /etc/my.cnf 10.117.201.80:/etc/my.cnf
-
Primary node configuration
In my CNF file
[mysqld] #Enable binlog log log-bin=mysql-bin #Determine host id server-id=1
-
Slave node configuration
On the slave node, you must specify a unique service id. The binary log can not be opened for the slave node. If it is opened, it can also be used for data backup and recovery.
[mysqld] server-id=2
-
Create replication user
The slave node needs a user on the master node to connect to the master node. Any user with replication slave permission can copy.
Log in to the master node, execute the following command, create a test user and grant replication slave permission.
mysql>create user 'test'@'%' identified by 'test' # The above% is to allow ip access to the external network. Generally, it is not recommended to do so in the internal network. It is generally set to the ip address of the slave server: 10.117.201.80 here mysql>grant replication slave on *.* to 'test'@'10.117.201.80'; #Assign all permissions to test user, ** Indicates that all databases can be synchronized, test indicates authorized users, and 10.117.201.80 indicates that this server can be synchronized mysql>flush privileges #Refresh permissions mysql>show master status #View the status of the master mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000003 | 2784 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) #mysql-bin. 00000 3 log name, 2784 log number
-
Specify the operations of copying master node and logging in slave node for slave node
mysql>change master to master_host='10.117.201.81',master_user='test',master_password='test',master_log_file='mysql-bin.000003',master_log_pos=2784; # master_host master node IP master_ test replication user created by user master node, master_password user password master_ log_ Log name in master file master node_ log_ POS master node position
-
Start slave node synchronization
mysql>start slave;
#If you need to stop the slave node synchronization, you can enter the MySQL > stop slave command
-
View slave node status
mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.117.201.81 Master_User: test Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 2784 Relay_Log_File: mon-longi-micro1-relay-bin.000012 Relay_Log_Pos: 320 Relay_Master_Log_File: mysql-bin.000003 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 2784 Relay_Log_Space: 704 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_UUID: d73d83b5-9b3f-11eb-a679-525400a1fd3f Master_Info_File: /usr/local/mysql/mysql5.7.30/data/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec)Find the above Slave_IO_Running: Yes,Slave_SQL_Running: Yes indicates that the master-slave synchronization configuration has been successful, so it needs to be verified; For example, insert a piece of data into a table in the test database of the master database and check whether there is new data in the same data table in the test database of the slave to verify whether the master-slave replication function is effective. You can also close the slave (MySQL > stop slave;), Then modify the master to see if the slave is modified accordingly (after the slave is stopped, the modifications of the master will not be synchronized to the slave), and then the verification of the master-slave replication function can be completed.
-
Insert a piece of data into the test table in the test database of the primary node
mysql> use test; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select * from test; +----+----------+------+ | id | name | age | +----+----------+------+ | 1 | zhangtao | 26 | | 2 | TT | 18 | | 3 | zt | 20 | | 4 | ml | 26 | +----+----------+------+ 4 rows in set (0.00 sec) mysql> insert into test values(5,'zhang',26); Query OK, 1 row affected (0.00 sec) mysql> select * from test; +----+----------+------+ | id | name | age | +----+----------+------+ | 1 | zhangtao | 26 | | 2 | TT | 18 | | 3 | zt | 20 | | 4 | ml | 26 | | 5 | zhang | 26 | +----+----------+------+ 5 rows in set (0.00 sec)
-
Log in from the node to check whether the same data exists
mysql> use test; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select * from test; +----+----------+------+ | id | name | age | +----+----------+------+ | 1 | zhangtao | 26 | | 2 | TT | 18 | | 3 | zt | 20 | | 4 | ml | 26 | | 5 | zhang | 26 | +----+----------+------+ 5 rows in set (0.00 sec)
It can be seen that the slave node 80 does have the data just inserted on the master node 81.
-
Verify that after the slave node is closed, the data of the master node is updated, and the slave node will not have data synchronization theoretically. Log in to the slave node for operation
mysql> stop slave; Query OK, 0 rows affected (0.00 sec) mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Master_Host: 10.117.201.81 Master_User: test Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 3053 Relay_Log_File: mon-longi-micro1-relay-bin.000012 Relay_Log_Pos: 589 Relay_Master_Log_File: mysql-bin.000003 Slave_IO_Running: No Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 3053 Relay_Log_Space: 973 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_UUID: d73d83b5-9b3f-11eb-a679-525400a1fd3f Master_Info_File: /usr/local/mysql/mysql5.7.30/data/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec)As can be seen from the above, I have turned off the synchronization of the slave node.
-
Log in to the master node to update the test table data in the test database.
mysql> update test set name='Sun WuKong' where id =1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from test; +----+-----------+------+ | id | name | age | +----+-----------+------+ | 1 | Sun WuKong | 26 | | 2 | TT | 18 | | 3 | zt | 20 | | 4 | ml | 26 | | 5 | zhang | 26 | +----+-----------+------+ 5 rows in set (0.00 sec)
It can be seen from the above that I have changed the main node. Observe whether the test table of the slave node test library has changed.
mysql> select * from test; +----+----------+------+ | id | name | age | +----+----------+------+ | 1 | zhangtao | 26 | | 2 | TT | 18 | | 3 | zt | 20 | | 4 | ml | 26 | | 5 | zhang | 26 | +----+----------+------+ 5 rows in set (0.00 sec)
It can be seen that the slave node has not changed, and the results are consistent as expected.
-
Now let's turn on slave synchronization
mysql> start slave; Query OK, 0 rows affected (0.00 sec) mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.117.201.81 Master_User: test Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 3345 Relay_Log_File: mon-longi-micro1-relay-bin.000013 Relay_Log_Pos: 612 Relay_Master_Log_File: mysql-bin.000003 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 3345 Relay_Log_Space: 1265 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_UUID: d73d83b5-9b3f-11eb-a679-525400a1fd3f Master_Info_File: /usr/local/mysql/mysql5.7.30/data/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec)Slave_IO_Running: Yes Slave_SQL_Running: YesYou can see that the synchronization is successful. Then make sure whether the data just updated by the master node has come and check the test table of the test library of the slave node.
mysql> select * from test; +----+-----------+------+ | id | name | age | +----+-----------+------+ | 1 | Sun WuKong | 26 | | 2 | TT | 18 | | 3 | zt | 20 | | 4 | ml | 26 | | 5 | zhang | 26 | +----+-----------+------+ 5 rows in set (0.00 sec)
It's really synchronized. Perfect.
-
Troubleshooting of error reporting during installation
For example, an error occurs when modifying the password in mysql:
mysql> use mysql; ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
Solution:
mysql> alter user 'root'@'localhost' identified by '123456'; ERROR 1819 (HY000): Your password does not satisfy the current policy requirements #If you want to set a simple test password, for example, set it to 123456, you will be prompted with this error. The error means that your password does not meet the requirements, which is actually related to validate_ password_ The value of policy
Solution:
mysql> set global validate_password_policy=0; Query OK, 0 rows affected (0.00 sec) #Modify validate_ password_ Value of policy parameter mysql> set global validate_password_length=1; Query OK, 0 rows affected (0.00 sec) #alidate_ password_ The password length parameter is 8 by default, and we modify it to 1 mysql> alter user 'root'@'localhost' identified by '123456'; Query OK, 0 rows affected (0.00 sec)
#The above is the problem of password modification; mysq5.7.x requires an initial password, which will be displayed on the command line during initialization.
-
Inconsistency between master and slave data, slave_ SQL_ Running: two solutions to no
1. The program may have written on the slave
2. It may also be caused by transaction rollback after the slave machine is restarted
It is generally caused by transaction rollback:
Solution 1:mysql> stop slave ; mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1; #Skip 1 error on slave mysql> start slave ;
Solution 2 (recommended):
First, stop the slave service, go to the master node to check the host status, and record the corresponding values of File and Position
#Enter master mysql> show master status\G; *************************** 1. row *************************** File: mysql-bin.000003 Position: 3345 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 1 row in set (0.00 sec)Then perform manual synchronization on the slave server:
mysql> change master to > master_host='10.117.201.81', > master_user='test', > master_password='test', > master_port=3306, > master_log_file=mysql-bin.000003', > master_log_pos=3345 ; 1 row in set (0.00 sec) mysql> start slave ; 1 row in set (0.00 sec)
View slave node status
mysql> show slave status\G *************************** 1. row *************************** ........ Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 3345 Relay_Log_File: localhost-relay-bin.000537 Relay_Log_Pos: 1034 Relay_Master_Log_File: mysql-bin.000003 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB:For more solutions to mysql master-slave replication problems, please refer to the following:
Summary of online MYSQL synchronization error handling methods
service mysql startup failed unit not found