Nowadays data volumes are growing very quickly. Today Hundreds of gigabytes database is not even being considered as a huge one. Naturally cloud services like Amazon Relational Database Service (RDS) or Google Cloud SQL services can be successfully used for such cases. This is very handy, you don’t even need to spend your time tuning…
All posts in mysql
MySQL: how to fix ‘ERROR 2006 (HY000) at line ##: MySQL server has gone away’
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.
MySQL: ERROR 1005 (HY000) at line 14: Can’t create table ‘example.tbl’ (errno: 150)
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…
MySQL: chained replication M1>S1>S2
In most case simple master-slave replication provides enough level of high-avalability (about 99.9%), read ops balancing and allows non-blocking backups (if you use MyISAM tables since InnoDB tables can be backed up without blocking). But sometimes you may want to setup chained replication with two slaves: first slave replicates from the master, and the second…
Dumping MySQL database from slave for replication set up
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…
MySQL: checking replication with mk-table-checksum
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…
Fixing ‘Missing dependency Cache::Cache at /usr/share/munin/plugins/mysql_’ error
While installing MySQL plugins for munin in Fedora 15 you may encounter this problem: [root@monitor plugins]# /usr/share/munin/plugins/mysql_ suggest Missing dependency Cache::Cache at /usr/share/munin/plugins/mysql_ line 716. [root@monitor plugins]# It means that your system lacks perl-Cache-Cache and perl-IPC-ShareLite packages. After installation you can proceed: yum install perl-Cache-Cache perl-IPC-ShareLite [root@monitor plugins]# /usr/share/munin/plugins/mysql_ suggest bin_relay_log commands connections files_tables innodb_bpool…