In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
2025-01-28 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >
Share
Shulou(Shulou.com)06/01 Report--
The following to understand the configuration of mysql database master-slave synchronous interactive operation, I believe you will benefit a lot after reading, the text in the essence is not much, hope to configure the mysql database master-slave synchronous interactive operation of this short content is what you want.
I. my.cnf file configuration
1. Modify the my.cnf configuration file. The log-bin,server-id cannot be opened for the main database 3306.
[root@mysql ~] # egrep "log-bin | server-id" / data/ {3306pr 3307} / my.cnf
/ data/3306/my.cnf:log-bin = / data/3306/mysql-bin
/ data/3306/my.cnf:server-id = 1
/ data/3307/my.cnf:#log-bin = / data/3307/mysql-bin
/ data/3307/my.cnf:server-id = 3
two。 Restart databases 3306 and 3307
[root@mysql ~] # / data/3306/mysql stop
[root@mysql ~] # / data/3306/mysql start
[root@mysql ~] # / data/3307/mysql stop
[root@mysql ~] # / data/3307/mysql start
3. Enter the main database 3306 and query whether log_bin is open and what the server_id is.
[root@mysql] # mysql-uroot-p123456-S / data/3306/mysql.sock
Mysql > show variables like "log_bin"
+-+ +
| | Variable_name | Value |
+-+ +
| | log_bin | ON |
+-+ +
1 row in set (0.01 sec)
Mysql > show variables like "server_id"
+-+ +
| | Variable_name | Value |
+-+ +
| | server_id | 1 | |
+-+ +
1 row in set (0.00 sec)
4. Create a user dedicated to synchronizing the database
Mysql > grant replicationslave on *. * to rep@'10.0.0.%' identified by '123456'
# *. * represents all libraries and all tables
Mysql > flush privileges
Mysql > select user,hostmysql.user
Mysql > show grants forrep@'10.0.0.%'
two。 Backup of main library 3306
Mysql > flush table with readlock; / / Lock the table, which is read-only and cannot be written. The mysql window cannot exit. If you exit, the locking table will fail. If you exceed the default locking time, the lock will be unlocked automatically.
Mysql > show variables like'timeout%'; / / View the default table locking time
Mysql > show master status; / / View binlog location
+-+
| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | |
+-+
| | mysql-bin.000004 | 328 | | |
+-+
1 row in set (0.00 sec)
[root@mysql] # mysqldump-uroot-p123456-S / data/3306/mysql.sock-A-B | gzip > / opt/bak_$ (date+%F). Sql.gz / / Open a new CRT window for backup
[root@mysql ~] # ls / opt
Bak_2017-06-28.sql.gz
After mysql > show master status; / / back up the data, check the binlog location again to make sure that no new data is written during this period
Mysql > unlock tables; / / unlock
three。 Restore the data backed up by the master database 3306 to the slave library 3307
1. Log in to 3307 and confirm that logbin is closed. Server id does not conflict with 3306.
[root@mysql] # mysql-uroot-p123456-S / data/3307/mysql.sock
Mysql > show variables like "log_bin"
+-+ +
| | Variable_name | Value |
+-+ +
| | log_bin | OFF |
+-+ +
1 row in set (0.01 sec)
Mysql > show variables like "server_id"
+-+ +
| | Variable_name | Value |
+-+ +
| | server_id | 3 | |
+-+ +
1 row in set (0.00 sec)
two。 Restore the data backed up by 3306 to 3307
[root@mysql ~] # cd / opt
[root@mysql opt] # ls
Bak_2017-06-28.sql.gz
[root@mysql opt] # gzip-dbak_2017-06-28.sql.gz
[root@mysql opt] # ls
Bak_2017-06-28.sql
[root@mysql opt] # mysql-uroot-p123456-S / data/3307/mysql.sock CHANGE MASTER TO
-> MASTER_HOST='10.0.0.20'
-> MASTER_PORT=3306
-> MASTER_USER='rep'
-> MASTER_PASSWORD='123456'
-> MASTER_LOG_FILE='mysql-bin.000004'
-> MASTER_LOG_POS=328
Mysql > start slave; / / Slave Library 3307 turns on the master-slave copy switch
Mysql > show slave status\ G / / check synchronization status
* * 1.row * *
Slave_IO_State: Waiting formaster to send event
Master_Host: 10.0.0.20
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 328
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 253
Relay_Master_Log_File: mysql-bin.000004
Slave_IO_Running:Yes / / confirm that these two entries are yes status
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB: mysql
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: 328
Relay_Log_Space: 403
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 / / number of seconds that the slave database is delayed than the master library during replication
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
1 row in set (0.00 sec)
Or check the synchronization status like this
[root@mysql ~] # mysql-uroot-p123456-S / data/3307/mysql.sock-e "show slave status\ G" | egrep-I "running | _ behind" / /-e does not need to log in to mysql to interactively type the command
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0
View thread status
[root@mysql] # mysql-uroot-p123456-S/data/3307/mysql.sock-e "show processlist\ G"
[root@mysql] # mysql-uroot-p123456-S/data/3306/mysql.sock-e "show processl
four。 test
Create a database on the main library 3306, and then log in to 3307 to see if it is synchronized properly. It has been tested that it has been synchronized properly ^ _ ^
After reading this article on how to configure the master-slave synchronous interactive operation of mysql database, many readers will want to know more about it. If you need more industry information, you can follow our industry information section.
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.
Views: 280
*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
© 2024 shulou.com SLNews company. All rights reserved.