Linux Ask! is a Q & A web site specific for Linux related questions. Questions are collected, answered and audited by experienced Linux users.
Create MySQL Dump that ignore duplicate key error when import back to MySQL
Answer:
When you import a dump file created by the mysqldump command, to a non-empty database, duplicate key error such as "Duplicate entry 'X' for key 'Y'" might occur.
To ignore this error, simply use the following command:
# mysqldump my_db my_table --insert-ignore
Solving the error "Packet too large" in MySQL
Answer:
When you try to insert a very large row of data (e.g. BLOB) into MySQL using a single insert statement, MySQL might give you the error "Packet too large". You might need to adjust the value of max_allowed_packet as needed.
1. Edit the MySQL configurations, e.g. /etc/my.cnf
..
max_allowed_packet=32M
..Don't forget to restart MySQL to take effect.
# /sbin/service mysqld restart
Reference: http://dev.mysql.com/doc/refman/5.5/en/packet-too-large.html
Setting the MySQL's Binary Log Format
Answer:
For MySQL 5.1.11 and earlier, and for MySQL 5.1.29 and later, statement-based logging is used by default as the binary log format. If you want to change it, e.g. to use the "row-based" format, do the following:
1. Edit the MySQL configurations, e.g. /etc/my.cnf
..
binlog_format=row
..Don't forget to restart MySQL to take effect.
# /sbin/service mysqld restart
Reference: http://dev.mysql.com/doc/refman/5.1/en/binary-log-setting.html
Unsafe SQL for MySQL's statement based replication
Answer:
Following are the list of MySQL's function which are unsafe if you are using statement based replication (which is the default in MySQL 5)
In short, if you are using MySQL's replication, do not use the following functions.
Reference: http://dev.mysql.com/doc/refman/5.1/en/replication-sbr-rbr.html
How to repair a broken MySQL replication?
Answer:
Sometimes you might discovered the slave MySQL server is having problem to replicate data from the master, when running the "SHOW SLAVE STATUS" command, it show:
mysql> SHOW SLAVE STATUS \G
..
Slave_IO_Running: Yes
Slave_SQL_Running: No
..
Last_Errno: 1146
Last_Error: Error 'Table 'db2.table3' doesn't exist' on query. Default database: 'db1'.
..
The above error message said the table "db2.table3" does not exist in the slave DB. In order to fix this error, we just simply ignore this error and resume the replication. To do so:
1. Stop the slave from replication
mysql> STOP SLAVE;
2. Skip the error
mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
3. Resume the replication
mysql> STARTSLAVE;
You will now check again the status using "SHOW SLAVE STATUS", and you will discover "Slave_SQL_Running: Yes" this time. Repeat the above steps if needed.
