====== MySQL / MariaDB Administration ======

===== backup / restore =====

<code bash>
# backup all databases
mysqldump -u root -p --all-databases | gzip -9 > [backupfile.sql.gz]

# backup specific DB
mysqldump -u root -p --databases db1 db2 db3 | gzip -9 > [backupfile.sql.gz]

# restore specific DB
gunzip < [backupfile.sql.gz] | mysql -u [uname] -p[pass] [dbname]

# import into already existing DB
mysqlimport -u [uname] -p[pass] [dbname] [backupfile.sql]
</code>

===== errors =====

==== 150 ====

>Error 150 is related to foreign key relations. Receiving this error means that a foreign key was defined in the table to be altered and it cannot be dropped as its properties are still valid. ([[https://justbugs.wordpress.com/2013/01/08/fixing-sql-errno-150/|source]])

<code mysql>
use databasename
SET FOREIGN_KEY_CHECKS = 0;
\. sqlscript.sql
SET FOREIGN_KEY_CHECKS = 1;
</code>

http://bugs.mysql.com/bug.php?id=16181

==== Cannot add or update a child row: a foreign key constraint fails ====
Check if the charsets and collations match.