“Live”-Insert of foreign keys. table2 contains foreign key fk_id referencing id column of table 1.

INSERT INTO table2 (fk_id,col2) SELECT id,'bar' FROM table1 WHERE col4=“Foo”;

1. /etc/init.d/mysql stop
2. mysqld_safe –skip-grant-tables &
3. mysql -u root

4. mysql> use mysql;
   mysql> update user set password=PASSWORD(”newrootpassword”) where user=’root’;
   mysql> flush privileges;
   mysql> quit
   

5. /etc/init.d/mysql stop
6. /etc/init.d/mysql start
7. mysql -u root -p newrootpassword

1. mysqldump –opt -Q -u<dbuser> -p <dbname> > ./$(hostname)_<dbname>_$(date +%Y%m%d_%H%M).sql
2. gzip $(hostname)_<dbname>_$(date +%Y%m%d_%H%M).sql

mysqldump --opt -Q -u<dbuser> -p <dbname> [<tablelist>]| bzip2 -cq9 > ./$(hostname)_<dbname>_$(date +%Y%m%d_%H:%M).sql.bz2

The c makes gzip/bzip2 read from stdin (and not some other file from disk), the q (quite) mutes gzip, so it does not blabber around, the 9 is the compression level (maximum compression).

Or gzipped:

.. | gzip -cq9 > ./$(hostname)_<dbname>_$(date +%Y%m%d_%H%M).sql.gz

Or lzma:

.. | lzma -cq9 > ./$(hostname)_<dbname>_$(date +%Y%m%d_%H%M).sql.gz

If no pipe is used, this will compress a normal file:

bzip2 -z9 <filename>

To see the progress:

mysqldump... | pv | bzip2...

Dumps the DB, compresses it and stores it on a server.

mysqldump --opt -Q -u<user> -p <dbname> | bzip2 -cq9 | ssh <host> "cat > ./$(hostname)_<dbname>_$(date +%Y%m%d_%H:%M).sql.bz2"

time mysqldump --opt -Q -uroot -psecret mydb > ./$(hostname)_mydb_$(date +%Y%m%d_%H%M).sql

Time (total) 26,6s

Resulting filesize 180MB

time mysqldump --opt -Q -uroot -psecret mydb | gzip -cq9 > ./$(hostname)_mydb_$(date +%Y%m%d_%H%M).sql.gz

Time (total) 35.6s

Resulting filesize 27MB

time mysqldump --opt -Q -uroot -psecret mydb | bzip2 -cq9 > ./$(hostname)_mydb_$(date +%Y%m%d_%H%M).sql.bz2

Time (total) 72.7s

Resulting filesize 21MB

time mysqldump --opt -Q -uroot -psecret mydb | lzma -cq9 > ./$(hostname)_mydb_$(date +%Y%m%d_%H%M).sql.lzma

Time (total) 971.4s

Resulting filesize 20MB

mysql -uuser -p dbname < dump.sql

bunzip2 < dump.sql.bz2 | mysql -uuser -p dbname

Condensated from http://www.howtoforge.com/mysql_database_replication and http://theindexer.wordpress.com/2009/02/20/master-slave-mysql-50-replication-on-ubuntu-intrepid-810/

1. /etc/mysql/my.cnf

   #bind-address = 127.0.0.1 # this line needs to be a comment
   log-bin       = /var/lib/mysql/master-bin.log
   log-bin-index = /var/lib/mysql/master-bin.index.log
   binlog-do-db  = <dbname1>
   binlog-do-db  = <dbname2>
   server-id     = 101
   

2. sudo /etc/init.d/mysql restart
3. mysql -uuser -p

   USE <dbname>;
   FLUSH TABLES WITH READ LOCK;
   SHOW MASTER STATUS;
   

For security reason the communication between master and slave should be encrypted. SSH tunnels, built upon a public key authentication between master and slave satisfy this constraint. The master port in the following example points to such a tunnel.

1. server-id (different from master!)
2. stop slave;
3. CHANGE MASTER TO MASTER_HOST='host', MASTER_PORT=3307, MASTER_USER='repl_user', MASTER_PASSWORD='password', MASTER_LOG_FILE='masterlogfile', MASTER_LOG_POS=masterlogpos;
4. start slave;
5. show slave status\G;

tail -f /var/log/syslog | grep “mysql”