Mysql master-slave installation configuration _ MySQL

Mysql master-slave installation configuration bitsCN. comMysql master-slave installation configuration environment: MySQL database versions on the master-slave server are the same as 5.1.34 host IP: 192.168.0.1 slave IP: 192.168.0.2
I. mySQL master server configuration 1. edit the configuration file/etc/my. cnf # make sure that the name of the database to be backed up is as follows: server-id = 1log-bin = mysql-binbinlog-do-db = mysql # if multiple databases are backed up, repeat this option to binlog-ignore-db = mysql # The database name that does not need to be backed up. if you back up multiple databases, repeat setting this option to log-slave-updates # this parameter must be added, otherwise it will not record the updates to the binary file. slave-skip-errors # skipping errors, continue copying
2. create a user mysql> grant replication slave on *. * to slave@192.168.0.2 identified by '000000'; # grant replication slave on *. * to 'username '@ 'host' identified by 'password'; # connection test on Slave: mysql-h 192.168.0.1-u test-p
3. LOCK the master database table mysql> flush tables with read lock;
4. displays the File and Position records of the master database, slave Database settings use ==================================== mysql> show master status; + metric + ---------- + -------------- + ---------------- + | File | Position | Binlog_do_db | usage | + usage + ---------- + -------------- + usage + | mysql-bin.000001 | 106 | + usage + -------- + -------------- + ------------------ +
5. open another terminal, package the master database cd/usr/local/mysql # mysql database directory tar zcvf var.tar.gz var ================== =====
II. configure MySQL from the server 1. transmit the data packet from the master database and unpack it # cd/usr/local/mysql # scp 192.168.0.1:/usr/local/mysql/var.tar.gz. # tar zxvf var.tar.gz
2. view and modify the var folder permissions # chown-R mysql: mysql var3. edit/etc/my. cnfserver-id = 2log-bin = mysql-binmaster-host = 192.168.0.1master-user = slavemaster-password = 1111master-port = 3306replicate-do-db = test # replicate-ignore-db = mysql # ignore database master-connect-retry = 60 # if the master server is disconnected from the server, reconnect time difference (SEC) log-slave-updates # this parameter must be added, otherwise it will not record the updates to the binary file slave-skip-errors # skip the error, continue copying
4. verify the connection to the MASTER # mysql-h192.168.0.1-uslave-ppasswordmysql> show grants for slave@192.168.0.2; 5. set synchronization settings on the SLAVE to connect to the MASTER MASTER_LOG_FILE as the File of the MASTER database, MASTER_LOG_POS is the Position ========================== mysql> slave stop; mysql> change master to MASTER_HOST = '2017. 168.0.1 ', MASTER_USER = 'Slave', MASTER_PASSWORD = '000000', MASTER_LOG_FILE ='
Mysql-bin.000001 ', MASTER_LOG_POS = 106; 6. start the SLAVE service mysql> slave start;
7. check the slave status mysql> show slave status/G. the values in the Slave_IO_Running and Slave_ SQL _Running columns are "Yes", indicating the Slave I/O and SQL threads.
All are running normally. 8. UNLOCK the master database table mysql> unlock tables. the master database is successfully created. You can insert data into the master database to test whether the synchronization is normal.
Common errors and solutions: troubleshooting of common problems: 1. show slave status/G on the slave database; the following situations occur: Slave_IO_Running: Yes Slave_ SQL _Running: No Seconds_Behind_Master: NULL cause:. the program may perform write operations on slave B. it may also be caused by transaction rollback after the server load balancer instance restarts. solution: Go to master mysql> show master status; + metric + ---------- + -------------- + ---------------- + | File | Position | Binlog_Do_DB | usage | + usage + ---------- + -------------- + usage + | mysql-bin.000040 | 324 | + usage + -------- + -------------- + ------------------ + then go to the slave server to manually synchronize the slave stop; change master to master_host = '10. 14.0.140 ', master_user = 'repl', master_password = '000000', master_port = 111111, master_log_file = 'MySQL-bin.000040', master_log_pos = 3306; slave start; show slave status/G; 2. symptom: The slave Database cannot be synchronized. the "show slave status" command displays "Slave_IO_Running" as "No" and "Seconds_Behind_Master ".
Solve with null: restart the master database service mysql restart mysql> show master status; + metric + ---------- + -------------- + ---------------- + | File | Position | Binlog_Do_DB | usage | + usage + ---------- + -------------- + usage + | mysql-bin.000001 | 98 | + usage + ---------- + -------------- + ---------------- + slave stop; change master to Master_Log_File = 'MySQL-bin.000001 ', Master_Log_Pos = 98 slave start; or: stop slave; set global SQL _slave_skip_counter = 1; start slave; this problem mainly occurs in the master database. in actual operations, I need to restart the master database and then restart slave to solve this problem,
In this case, you must restart the master database.
1. the primary and secondary databases are synchronized mainly through binary logs. 2. when starting the secondary database, you must synchronize the data and delete the master.info file in the log directory. Because master.info records
The information of the master database to be connected last time. if it is not deleted, it does not work even if it is modified in my. cnf. Because the read is still
The information in the master.info file. In the mysql replication environment, there are eight parameters that can be controlled. to copy or ignore databases or tables that do not need to be copied, set the following two parameters on the 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 reduction of Io and network IO caused by Binlog records on the Master side, it also reduces the number of I/O threads and SQL threads on the slave end,
This greatly improves the replication performance. The disadvantage is that mysql determines whether to copy an event not based on the DB where the query that generates the event is located, but based on the time when the query is executed.
The default database (that is, the database name specified at login or the database specified in running "use database"), only
When the specified DB is completely consistent, the IO thread will read the event to the slave IO thread.
When the DB is different, the data in a Table in the DB that needs to be copied is changed. this event will not be copied to the Slave. in this way
The data on the Slave end is inconsistent with that on the Master node. Similarly, 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 end. when the slave end does not have the database, it will cause a replication error and stop. the following six items need to be set on slave: Replicate_Do_DB: Set the database to be copied. multiple databases are separated by commas (,) Replicate_Ignore_DB: Set the databases that can be ignored. replicate_Do_Table: set the Table Replicate_Ignore_Table to be copied. set the Table Replicate_Wild_Do_Table to be ignored. the function is the same as Replicate_Do_Table, but it can be set with wildcards. Replicate_Wild_Ignore_Table: The function is the same as Replicate_Do_Table. the function is the same as Replicate_Ignore_Table and can contain wildcards. The advantage is that the replication filtering mechanism is set on the slave side to ensure that Slave and Master data are not caused by default database problems.
Inconsistency or replication errors. The disadvantage is that the performance is worse than that on the Master end. The reason is that the event will be read to the Slave end by the IO thread no matter whether replication is required or not,
This not only increases the network I/O volume, but also increases the Relay Log write volume for the I/O thread on the Slave side. the synchronization principle indicates that MySQL Replication tracks all database changes (update and delete) in binary logs based on the master server ). MySQL uses three threads to complete Replication. the specific distribution is the master thread and the last two threads; the main thread can be understood as the Binlog Dump thread in the output of show processlist on the master server, IO and slave server respectively.
SQL thread; the master server creates and sends the content in the binlog to the slave server. Read from the server I/O thread from the Binlog Dump thread of the master server
And copy the data to the relay-log file in the server data directory. the SQL thread is used to read relay logs.
And execute the updates contained in the log. MySQL Replication is one-way. The asynchronous synchronization mechanism of MySQL is based on the master to update and delete all databases.) it is recorded in binary logs. Therefore, you want to enable synchronization
The binary log must be enabled on the master node. Each slave receives updates recorded in binary logs from the master,
Therefore, a copy of this operation is executed on the slave. It should be very important to realize that binary logs are only enabled from binary logs.
The update operation is recorded at the beginning of the time. All slave data must be copied from the master when binary logs are enabled.
If the data on the slave is inconsistent with that on the master node when binary logs are enabled during synchronization, the slave synchronization will fail.
One of the methods to copy DATA on the master is to execute the load data from master statement on slave. However, you must note that load data from master is available only after MySQL 4.0.0, and only MyISAM tables on the master are supported. Similarly,
This operation requires a global read lock, so that no update operation will be performed on the master when the log is transferred to slave. When
During hot backup of a free lock table (in MySQL 5.0), it is unnecessary to read the global lock. Because of these restrictions, we recommend that you only
The load data from master statement is executed only when the related DATA is small, or a long read lock is allowed on the master.
Since each system loads data from the MASTER at different speeds, a good measurement rule is that 1 MB of DATA can be copied per second.
This is just a rough estimate, but the master and slave are both Pentium 100 MHz machines and can achieve this when they are connected using a MBit/s network.
Speed. After the master data has been fully copied on the slave, you can connect to the master and wait for updates. If the master
When the server or server load balancer is disconnected, the server load balancer periodically tries to connect to the master node until it can be reconnected and waits for updates. The retry interval is controlled by the-master-connect-retry option. the default value is 60 seconds. Each slave records the log location when it is disabled.
The master does not know how many slave connections are established or when the slave is updated.
The MySQL synchronization function is implemented by three threads (one on the master node and two on the slave node. After the start slave statement is executed, slave is created.
An I/O thread. The I/O thread connects to the master and requests the master to send statements in binary logs. The master creates a thread
The log content is sent to the slave. This thread executes the Binlog Dump in the result after the show processlist statement is executed on the master.
Thread is. The I/O thread on slave reads the statements sent by the Binlog Dump thread of the master and copies them to its data directory.
In the relay logs. The third is the SQL thread, which salve uses to read relay logs and then execute them to update data.
As mentioned above, each mster/slave has three threads. Each master has multiple threads. it creates a thread for each slave connection, and each slave has only I/O and SQL threads. Before MySQL 4.0.2, only two threads (one master and one slave) are required for synchronization ). I/O on slave
It is merged with the SQL thread and does not use relay logs. The advantage of using two threads on slave is to split the read log and execution into two
Independent tasks. If the task is executed slowly, the log reading task will not be slowed down. For example, if slave is stopped for a period of time
The I/O thread can quickly read all logs from the master after the slave is started, although the SQL thread may lag behind the I/O thread for several hours.
If slave stops running all the SQL threads, but the I/O thread has read all the update logs and saved them in the local
After the log (relay-log) is in, slave will continue to execute them after it is started again. This allows you to clear binary data on the master.
Because the slave does not need to read the update log from the master. The execution of the show processlist statement will tell us what happens on the master and slave.
Author: zixinlibitsCN.com