2,187
edits
Difference between revisions of "MySQL"
Jump to navigation
Jump to search
Removed categories
(→Maintenance: Added "Export / Backup") |
(Removed categories) |
||
| (6 intermediate revisions by the same user not shown) | |||
| Line 1: | Line 1: | ||
{{Depreciated|category=MySQL}} | |||
See the official [http://dev.mysql.com/doc/ MySQL documentation] for further info. | See the official [http://dev.mysql.com/doc/ MySQL documentation] for further info. | ||
| Line 294: | Line 296: | ||
# Exclude certain tables | # Exclude certain tables | ||
mysqldump -u root -p dbname --ignore-table=dbname.table1 --ignore-table=dbname.table2 > dbname.sql | mysqldump -u root -p dbname --ignore-table=dbname.table1 --ignore-table=dbname.table2 > dbname.sql | ||
# Export a table as CSV | |||
SELECT * INTO OUTFILE '/tmp/table.csv' | |||
FIELDS TERMINATED BY ',' ENCLOSED BY '' ESCAPED BY '\\' | |||
LINES TERMINATED BY '\n' FROM table; | |||
</source> | </source> | ||
| Line 305: | Line 312: | ||
<source lang="mysql"> | <source lang="mysql"> | ||
OPTIMIZE TABLE SystemEvents; | OPTIMIZE TABLE SystemEvents; | ||
</source> | |||
=== Skip Replication Errors === | |||
Sometimes replication to a Slave will stop due to an error. To skip the error and restart replication, perform the following on the slave... | |||
<source lang="mysql"> | |||
STOP SLAVE; | |||
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; | |||
START SLAVE; | |||
</source> | |||
=== Restart Replication === | |||
If replication is completely broken, or the slave no longer has an accurate copy of the master. | |||
# Create dump from master | |||
#* <code> mysqldump -u root -p --master-data --all-databases --flush-privileges | gzip -1 > /var/tmp/replication.sql.gz </code> | |||
# Copy to slave | |||
#* EG <code> scp /var/tmp/replication.sql.gz root@10.1.1.55:/var/tmp/. </code> | |||
# Stop slave if still running | |||
#* In MySQL - <code>STOP SLAVE;</code> | |||
# Import into slave | |||
#* <code> zcat /var/tmp/replication.sql.gz | mysql -u root -p</code> | |||
# Start slave | |||
#* In MySQL - <code>START SLAVE;</code> | |||
=== Remove Old Binary Logs === | |||
Make sure that any Slave's are up to date before deleting old binary logs | |||
<source lang="mysql"> | |||
SHOW MASTER STATUS\g | |||
SHOW MASTER LOGS; # Get list of logfiles | |||
PURGE MASTER LOGS TO 'mysql-bin.012344'; # Removes all logs up to the one specified | |||
</source> | </source> | ||
| Line 325: | Line 362: | ||
To create a blank copy of a database (ie with a schema but no data) follow the procedure above but use the <code>-d</code> option when creating the dump, so | To create a blank copy of a database (ie with a schema but no data) follow the procedure above but use the <code>-d</code> option when creating the dump, so | ||
* <code> mysqldump -u root -p -d sourcedb > sourcedb-schema.sql </code> | * <code> mysqldump -u root -p -d sourcedb > sourcedb-schema.sql </code> | ||