In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
2025-01-24 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >
Share
Shulou(Shulou.com)05/31 Report--
This article introduces the relevant knowledge of "how to install and configure Mysql master and slave". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!
Environment:
The MySQL version on the master and slave server is 5.1.34.
Host IP:192.168.0.1
Slave IP:192.168.0.2
one。 MySQL master server configuration
1. Edit configuration file / etc/my.cnf
# make sure it looks like the following line
Server-id = 1
Log-bin=-bin
Binlog-do-db=mysql # the name of the database that needs to be backed up. If you back up multiple databases, you can set this option repeatedly.
Binlog-ignore-db=mysql # does not need the database name to be backed up. If you back up multiple databases, you can set this option repeatedly.
The parameter log-slave-updates # must be added, otherwise the updated records will not be added to the binary file
Slave-skip-errors # skips the error and continues with the copy operation
two。 Establish a user
Mysql > grant replication slave on *. * to identified by '111111'
# grant replication slave on *. * to 'username host' identified by 'password'
# connection test can be done on Slave: mysql-h 192.168.0.1-u test-p
3. Lock the main database table
Mysql > FLUSH TABLES WITH READ LOCK
4. Display the main library information
Record File and Position, slave library settings will be used
=
Mysql > SHOW MASTER STATUS
+-+
| | File | Position | Binlog_do_db | Binlog_ignore_db | |
+-+
| | mysql-bin.000001 | 106 | |
+-+
5. Open another terminal and package the main library
Cd / usr/local/mysql # mysql library directory
Tar zcvf var.tar.gz var
= =
II. MySQL slave server configuration
1. Transfer and unpack the data packets from the main library
# cd / usr/local/mysql
# scp 192.168.0.1:/usr/local/mysql/var.tar.gz.
# tar zxvf var.tar.gz
2. View and modify var folder permissions
# chown-R mysql:mysql var
3. Edit / etc/my.cnf
Server-id=2
Log-bin=mysql-bin
Master-host=192.168.0.1
Master-user=slave
Master-password=111111
Master-port=3306
Replicate-do-db=test # the name of the database to be backed up
Replicate-ignore-db=mysql # ignored databases
Master-connect-retry=60 # if the slave server finds that the master server is down, the time difference to reconnect (seconds)
The parameter log-slave-updates # must be added, otherwise the updated records will not be added to the binary file
Slave-skip-errors # skips the error and continues with the copy operation
4. Verify the connection to MASTER
# mysql-h292.168.0.1-uslave-ppassword
Mysql > show grants for
5. Set synchronization on SLAVE
Set the File,MASTER_LOG_POS connected to MASTER MASTER_LOG_FILE as the primary library as the Position of the primary library
= =
Mysql > slave stop
Mysql > CHANGE MASTER TO MASTER_HOST=192.168.0.1,MASTER_USER=slave,MASTER_PASSWORD=111111,MASTER_LOG_FILE=mysql-bin.000001,MASTER_LOG_POS=106
6. Start the SLAVE service
Mysql > slave start
7. View SLAVE status
Mysql > SHOW SLAVE STATUSG
The values of both the Slave_IO_Running and Slave_SQL_Running columns are "Yes", indicating that both the Slave Imax O and SQL threads are running normally.
8. Unlock the main library table
Mysql > UNLOCK TABLES
The master-slave library has been built successfully. You can insert data on the main library to test whether synchronization is normal.
Common mistakes and solutions:
Handling of frequently asked questions:
1: the following occurs on the show slave statusG; above the slave library
Slave_IO_Running: Yes
Slave_SQL_Running: No
Seconds_Behind_Master: NULL
Reason:
a. The program may have written on slave.
b. It may also be caused by the rollback of the transaction after the slave machine is restarted.
Solution:
Enter master
Mysql > show master status
+-+
| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | |
+-+
| | mysql-bin.000040 | 324 |
+-+
Then perform a manual synchronization on the slave server
Slave stop
Change master to
Master_host=10.14.0.140
Master_user=repl
Master_password=111111
Master_port=3306
Master_log_file=mysql-bin.000040
Master_log_pos=324
Slave start
Show slave statusG
2. Phenomenon: unable to synchronize from the database. Show slave status shows that Slave_IO_Running is No,Seconds_Behind_Master and null
Solution: restart the master database
Service mysql restart
Mysql > show master status
+-+
| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | |
+-+
| | mysql-bin.000001 | 98 | |
+-+
Slave stop
Change master to Master_Log_File=mysql-bin.000001,Master_Log_Pos=98
Slave start
Or something like this:
Stop slave
Set global sql_slave_skip_counter = 1
Start slave
This phenomenon is mainly due to the problem with the master database. In practice, I can solve this problem by restarting master and then restarting slave. If this problem occurs, it is necessary to restart the master database.
1. The synchronization of primary and secondary libraries is mainly achieved through binary logs.
two。 When starting the child library, you must first synchronize the data and delete the: master.info file in the log directory. Because master.info records the information about the last time you want to connect to the main library, if you don't delete it, it won't work even if you make changes in my.cnf. Because it is still reading the information in the master.info file.
In a mysql replication environment, there are eight parameters that we can control. The DB or table that need to be replicated or ignored are:
The following two items need to be set on Master:
Binlog_Do_DB: set which databases need to record Binlog
Binlog_Ignore_DB: set where the database does not need to record Binlog
The advantage is that the amount of Io and network IO caused by the Binlog records on the Masterside are reduced, and the IO threads and SQL threads on the slave side are reduced, thus greatly improving the replication performance.
The disadvantage is that mysql judges whether an event needs to be copied not according to the DB of the query that generated the event, but according to the default database where the query is executed (that is, the library name specified when logging in or the DB specified in "use database"). Only when the current default DB matches the DB set in the configuration will the IO thread read the event to the slave I / O thread. Therefore, if the data in a Table in the DB to be copied is changed when the default DB is different from the DB to be copied, the event will not be copied to the Slave, which will cause the data on the Slave side to be inconsistent with that of the Master. Similarly, if the data in the database that does not need to be copied is changed under the default database, it will be copied to the slave side, and when the server does not have the database, it will cause replication error and stop.
The following six items need to be set on slave:
Replicate_Do_DB: sets the database to be replicated. Multiple DB are separated by commas.
Replicate_Ignore_DB: sets the database that can be ignored.
Replicate_Do_Table: set the Table to be copied
Replicate_Ignore_Table: sets the Table that can be ignored
Replicate_Wild_Do_Table: the function is the same as Replicate_Do_Table, but can be set with wildcards.
Replicate_Wild_Ignore_Table: function is the same as Replicate_Do_Table, function is the same as Replicate_Ignore_Table, and wildcards can be used.
The advantage is that the replication filtering mechanism is set up on the server side, which can ensure that the data inconsistency or replication errors between Slave and Master will not occur because of the default database problems.
The disadvantage is that the performance is worse than on the Master side. The reason is that events will be read to the server by the IO thread regardless of whether they need to be copied or not, which not only increases the amount of network IO, but also increases the amount of Relay Log writing to the server's IO thread.
Explanation of synchronization principle
MySQL's Replication is based on the primary server tracking all changes to the database (updates, deletions, etc.) in the binary log.
MySQL uses three threads to complete Replication work. The specific distribution is one related thread on the master and two related threads on the slave.
The related threads of the master can understand that the Binlog Dump thread and the slave server are IO and SQL threads in the output of SHOW PROCESSLIST on the master server.
The master server creation sends the contents of the binlog to the slave server. The content sent by the master server Binlog Dump thread is read from the server Icano thread and copied to the relay log file (relay-log) in the slave server data directory. The SQL thread is used to read the relay log and perform the updates contained in the log.
The Replication of MySQL is one-way, asynchronous synchronous.
The MySQL synchronization mechanism records all updates, deletions, etc., to the database based on master in the binary log. Therefore, to enable the synchronization mechanism, binary logging must be enabled in master. Each slave accepts an update operation recorded in the binary log on the master, so a copy of this operation is performed on the slave. It is important to be aware that binary logging records update operations only from the moment binary logging is enabled. All slave must copy the data that already exists on the master when binary logging is enabled. If the data on slave when synchronization is running is inconsistent with that when binary logging is enabled on master, then slave synchronization will fail. One of the ways to copy data from master is to execute LOAD DATA FROM MASTER statements on slave. Note, however, that LOAD DATA FROM MASTER has been available since MySQL 4.0.0, and only supports MyISAM type tables on master. Similarly, this operation requires a global read lock so that there is no update operation on the master when the log is sent to slave. When a free lock table hot backup is implemented (in MySQL 5. 0), global read locks are not necessary. Because of these limitations, we recommend that LOAD DATA FROM MASTER statements be executed only when the relevant data is small on master, or allow a long read lock on master. Since the speed of LOAD DATA FROM MASTER varies from system to system, a better measure rule is to copy 1MB data per second. This is only a rough estimate, but both master and slave are Pentium 700MHz machines and can achieve this speed when connected to the 100MBit/s network. Once the master data has been fully copied on the slave, you can connect to the master and wait for updates to be processed. If the master crashes or the slave connection is disconnected, slave periodically attempts to connect to the master until it can reconnect and wait for updates. The interval between retries is controlled by the-master-connect-retry option, which defaults to 60 seconds. Each slave records the location of the log when it was closed. Master does not know how many slave connections or which slave will be updated.
The MySQL synchronization function is implemented by 3 threads (1 on master and 2 on slave). After the START SLAVE statement is executed, slave creates an iUnip O thread. The Icano thread connects to the master and requests the master to send the statements in the binary log. Master creates a thread to send the contents of the log to the slave. The Binlog Dump thread in the result of this thread executing the SHOW PROCESSLIST statement on master is. The I / O thread on slave reads the statements sent by the Binlog Dump thread of master and copies them to the relay log (relay logs) under its data directory. The third is the SQL thread, which salve uses to read the relay logs and then execute them to update the data. As mentioned above, there are three threads on each mster/slave. There are multiple threads on each master, which creates one thread for each slave connection, and each slave has only Icano and SQL threads. Before MySQL 4.0.2, synchronization required only two threads (one for master and one for slave). The isign O and SQL threads on slave are merged into one, and it does not use relay logs. The advantage of using two threads on slave is to separate log reading and execution into two separate tasks. If the execution of the task is slow, the log reading task will not slow down. For example, if slave is stopped for a period of time, the SQL O thread can read all the logs from the master soon after the master starts, although the SQL thread may lag several hours behind the master thread. If the slave stops before the SQL thread has finished executing, but the slave O thread has read and saved all the update logs in the local relay log (relay-log), it will continue to execute them after SQL starts again. This allows the binary log to be cleared on master because slave no longer needs to go to master to read the update log. Executing the SHOW PROCESSLIST statement tells us what is happening on master and slave that we are concerned about.
This is the end of the content of "how to install and configure Mysql master and slave". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!
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: 0
*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.