====== MySQL ======
===== Query =====
==== Foreign Keys ====
"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";
===== Reset Root Password (long way) =====

  - ''/etc/init.d/mysql stop''
  - ''mysqld_safe --skip-grant-tables &''
  - ''mysql -u root''
  - <code>
mysql> use mysql;
mysql> update user set password=PASSWORD(”newrootpassword”) where user=’root’;
mysql> flush privileges;
mysql> quit
</code>
  - ''/etc/init.d/mysql stop''
  - ''/etc/init.d/mysql start''
  -''mysql -u root -p newrootpassword''
===== Create Backup =====

==== By Hand ====
  - ''mysqldump --opt -Q -u<dbuser> -p <dbname> > ./$(hostname)_<dbname>_$(date +%Y%m%d_%H%M).sql''
  - ''gzip $(hostname)_<dbname>_$(date +%Y%m%d_%H%M).sql''

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

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:
<code bash>.. | gzip -cq9 > ./$(hostname)_<dbname>_$(date +%Y%m%d_%H%M).sql.gz</code>

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

If no pipe is used, this will compress a normal file:
<code bash>bzip2 -z9 <filename></code>

To see the progress:
<code bash>mysqldump... | pv | bzip2...</code>

==== Automatic with transfer ====
Dumps the DB, compresses it and stores it on a server.
<code bash>mysqldump --opt -Q -u<user> -p <dbname> | bzip2 -cq9 | ssh <host> "cat > ./$(hostname)_<dbname>_$(date +%Y%m%d_%H:%M).sql.bz2"</code>

==== Observations ====
=== Plain dump ===
<code bash>
time mysqldump --opt -Q -uroot -psecret mydb > ./$(hostname)_mydb_$(date +%Y%m%d_%H%M).sql
</code>
**Time (total)**        **26,6s** 

**Resulting filesize**  **180MB** 


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

**Time (total)** **35.6s**

**Resulting filesize**  **27MB** 

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

**Time (total)**  **72.7s** 

**Resulting filesize**  **21MB** 

=== lzma dump ===

<code bash>
time mysqldump --opt -Q -uroot -psecret mydb | lzma -cq9 > ./$(hostname)_mydb_$(date +%Y%m%d_%H%M).sql.lzma
</code>
**Time (total)**  **971.4s** 

**Resulting filesize**  **20MB** 


===== Restore a Backup =====
==== Uncompressed backup ====
''mysql -u//user// -p //dbname// < dump.sql''

==== Compressed backup ====
''bunzip2 < dump.sql.bz2 | mysql -u//user// -p //dbname//''

===== Replication =====
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/]]

==== Master Setup ====
  - //''/etc/mysql/my.cnf''//<code>
#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
</code>
  - ''sudo /etc/init.d/mysql restart''
  - ''mysql -u//user// -p''<code>
USE <dbname>;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
</code>

==== Client Setup ====

For security reason the communication between master and slave should be encrypted.  [[http://www.semok.de/ssh#ssh_tunnel_e.g._for_remote_mysql|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.

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

==== Debugging ====
''tail -f /var/log/syslog | grep "mysql"''