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

The following content mainly brings you some summary of the mysql thread, the knowledge here is slightly different from books, are summed up by professional and technical personnel in the process of contact with users, have a certain experience sharing value, hope to bring help to the majority of readers.

1. Check the log and some commands

1, show master status\ G

The main thing here is to see whether the files in log-bin are the same.

Show slave status\ G

The main purpose here is to see:

Slave_IO_Running=Yes

Slave_SQL_Running=Yes

If all are Yes, the configuration is successful.

2, enter show processlist\ G on master

Mysql > SHOW PROCESSLIST\ G

* 1. Row**

Id: 2

User: root

Host: localhost:32931

Db: NULL

Command: Binlog Dump

Time: 94

State: Has sent all binlog to slave;waiting for binlog to

Be updated

Info: NULL

If Command: Binlog Dump appears, the configuration is successful.

Stop slave # stop synchronization

Start slave # starts synchronization and updates from the location where the log terminates.

| SET SQL_LOG_BIN=0 | 1 # host running, which requires super permission. It is used to open and stop logs and open and stop logs at will, which will cause inconsistency of host slave data and cause errors. |

The SET GLOBAL SQL_SLAVE_SKIP_COUNTER=n # client runs to skip several events and can be executed only if the synchronization process is stopped due to an error.

RESET MASTER # runs on the host side to clear all logs. This command is the original FLUSH MASTER.

RESET SLAVE # runs from the computer, clears the log synchronization location flag and regenerates the master.info

Although the master.info has been regenerated, it is not useful. It is best to restart the mysql process of the slave machine.

LOAD TABLE tblname FROM MASTER # runs from the slave and rereads the data of the specified table from the host side. Only one table can be read at a time. Due to the limitation of timeout time, it is necessary to adjust the timeout time. To execute this command, you need to synchronize the account with reload and super permissions. And have select permission to the corresponding library. If the table is large, increase the values of net_read_timeout and net_write_timeout

LOAD DATA FROM MASTER # executes from the slave and re-reads all the data from the host. To execute this command, you need to synchronize the account with reload and super permissions. And have select permission to the corresponding library. If the table is large, increase the values of net_read_timeout and net_write_timeout

CHANGE MASTER TO master_def_list # changes some host settings online, multiple of which are separated by commas, such as

CHANGE MASTER TO

MASTER_HOST='master2.mycompany.com'

MASTER_USER='replication'

MASTER_PASSWORD='bigs3cret'

MASTER_POS_WAIT () # Slave operation

SHOW MASTER STATUS # host runs, see log export information

The SHOW SLAVE HOSTS # host runs, depending on the connected slave.

SHOW SLAVE STATUS (slave)

SHOW MASTER LOGS (master)

SHOW BINLOG EVENTS [IN 'logname'] [FROM pos] [LIMIT [offset,] rows]

PURGE [MASTER] LOGS TO 'logname'; PURGE [MASTER] LOGS BEFORE' date'

Second, common mistakes

1. An error message appears,

Slave I retries O: error connecting to master 'backup@192.168.1.x:3306'-retry-time: 60 retries: 86400, Error_code: 1045

Solution method

Delete all binary log files from the CVM, including master.info files in a data directory and files at the beginning of hostname-relay-bin.

Master.info:: records the log file on the Mysql master server and the location of the record and the password for the connection.

2. Error prompt appears

Error reading packet from server: File'/ home/mysql/mysqlLog/log.000001' not found (Errcode: 2) (server_errno=29)

Solution:

Because the primary server has been running for a period of time, the binaries are generated, and slave is read from log.000001, deleting the host binaries, including the log.index file.

3. The error prompt is as follows

Slave SQL: Error 'Table' xxxx' doesn't exist' on query. Default database: 't591. Query: 'INSERT INTO `xxxx` (type,post_id,browsenum) SELECT type,post_id,browsenum FROM xxxx WHERE hitdate='20090209'', Error_code: 1146

Solution method

Since slave does not have this table table, add this table and use slave start to continue synchronization.

4. The error prompt is as follows

Error 'Duplicate entry' 1 'for key 1' on query. Default database: 'movivi1'. Query: 'INSERT INTO `v1vid0roomuser _ samename`VALUES (null,1,'123','11','4545','123')'

Error 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near''at line 1'on query. Default database: 'club'. Query: 'INSERT INTO club.point_process (GIVEID, GETID, POINT, CREATETIME, DEMO) VALUES (0, 4971112, 5,' 2010-12-19 16-12-19 16-29-12-28) VALUES

1 row in set (0.00 sec)

Mysql > Slave status\ G

Display: Slave_SQL_Running is NO

Solution:

Mysql > stop slave

Mysql > set global sql_slave_skip_counter = 1

Mysql > start slave

5. The error prompt is as follows

# show slave status\ G

Master_Log_File: mysql-bin.000029

Read_Master_Log_Pos: 3154083

Relay_Log_File: c7-relay-bin.000178

Relay_Log_Pos: 633

Relay_Master_Log_File: mysql-bin.000025

Slave_IO_Running: Yes

Slave_SQL_Running: No

Replicate_Do_DB: club

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 1594

Last_Error: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running' mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave.

Skip_Counter: 0

Exec_Master_Log_Pos: 1010663436

The reason for this problem is that the main database suddenly stops or the problem terminates, the mysql-bin.xxx log is changed, and the slave server cannot find the file. You need to find the synchronized point and log file, and then chage master.

Solution:

Change master to

Master_host='211.103.156.198'

Master_user=' sync account'

Master_password=' synchronization password'

Master_port=3306

Master_log_file='mysql-bin.000025'

Master_log_pos=1010663436

6. The error prompt is as follows

Error 'Unknown column' qdir' in 'field list'' on query. Default database: 'club'. Query: 'insert into club.question_del (id, pid, ques_name, givepoint, title, subject, subject_pid, createtime, approve, did, status, intime, order_d, endtime,banzhu_uid,banzhu_uname,del_cause,qdir) select id, pid, ques_name, givepoint, title, subject, subject_pid, createtime, approve, did, status, intime, order_d, endtime,'1521859','admin0523',' meaningless reply', qdir from club.question where id=7330212'

1 row in set (0.00 sec)

This error means that there is no qdir field in the club.question_del table.

Query Desc club.question_del in the main mysql:

Execute on the wrong slave server: alter table question_del add qdir varchar (30) not null

7. The error prompt is as follows

Slave_IO_Running: NO

This error is that the IO process is not connected, find a way to connect the handle and the main POS number and file must be correct, and then reload the data. Specific steps:

Slave stop

Change master to master_host='IP address', master_user='club',master_password='mima',master_log_file='mysqld-bin.000048',MASTER_LOG_POS=396549485

Note: master_log_file='mysqld-bin.000048',MASTER_LOG_POS=396549485; is checked from the main: show master status\ G

LOAD DATA FROM MASTER

Load data from master

Slave start

Problem solved!

For the above summary of MySQL threads, if you have more to know, you can continue to pay attention to the innovation of our industry. If you need professional answers, you can contact the pre-sale and after-sale ones on the official website. I hope this article can bring you some knowledge updates.

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.