If you get mentioned error while restoring big database, make sure you have enough size of max_allowed_packet. [mysqld] max_allowed_packet=64M After adding it to your /etc/my.cnf restart MySQL server to apply changes.
If you get an error ERROR 1005 (HY000) at line 14: Can’t create table example.tbl (errno: 150) it could be for at least two reasons. MySQL doesn’t allow to create foreign keys for a set of tables one of which doesn’t exist. To solve this restriction you can set SET foreign_key_checks to 0: mysql> SET [...]
Usually to set up MySQL replication it’s convenient to dump database from current master with –master-data parameter. With this option mysqldump includes commented SQL operator which needs to be issued on slave to set up replication: CHANGE MASTER TO MASTER_LOG_FILE=’mysqld-bin.000008′, MASTER_LOG_POS=687808977; But sometime it’s not very good idea to dump database from master server. For [...]
Here is a simple way to check if your database on master and slave server have the same data. We will use mk-table-checksum from maatkit tools for this purpose. Note! Never do it on the production server! Or use it on your own risk! As stated: mk-table-checksum executes queries that cause the MySQL server to [...]
Everybody knows – backups are very important. Today a lot of web projects use MySQL to keep data. So you need to know how to set up reliable but simple backup of all your databases. And even if you are only developing you might probably need some tools to make quick dumps and to restore [...]
