2,187
edits
Difference between revisions of "Maintenance (MySQL)"
Jump to navigation
Jump to search
→Migrate Databases: Added "Ignore Foreign Key Checks"
(→Export / Backup: Added Create a Copy of a Table) |
(→Migrate Databases: Added "Ignore Foreign Key Checks") |
||
| (2 intermediate revisions by the same user not shown) | |||
| Line 3: | Line 3: | ||
<source lang="mysql"> | <source lang="mysql"> | ||
SHOW STATUS; # Shows general info | SHOW STATUS; # Shows general info | ||
SHOW VARIABLES LIKE "%version%"; # Show running MySQL version | |||
SHOW ENGINE INNODB STATUS \G # Show InnoDB database status | |||
SHOW DATABASES; # Show databases | SHOW DATABASES; # Show databases | ||
SHOW TABLES; # Show tables in the current database | SHOW TABLES; # Show tables in the current database | ||
| Line 112: | Line 114: | ||
# Give you client server access to the new database | # Give you client server access to the new database | ||
#* <code> GRANT ALL PRIVILEGES on destdb.* to 'destdbuser'@'clientsvr' IDENTIFIED BY 'password'; </code> | #* <code> GRANT ALL PRIVILEGES on destdb.* to 'destdbuser'@'clientsvr' IDENTIFIED BY 'password'; </code> | ||
=== Ignore Foreign Key Checks === | |||
If you receive an error similar to the following when trying to reimport a dump, its likely that you are falling fowl of foreign key checks which will fail as the table with the foreign key doesn't appear until later in the dump file | |||
* <code>ERROR 1005 (HY000) at line 14475: Can't create table 'db.table' (errno: 150)</code> | |||
Before starting the dump, disable foreign key checks | |||
* <code> mysql -u root -p -e "SET GLOBAL foreign_key_checks=0"</code> | |||
Then re-enable once all finished | |||
* <code> mysql -u root -p -e "SET GLOBAL foreign_key_checks=1"</code> | |||
== Create Blank Copy == | == Create Blank Copy == | ||