Difference between revisions of "Retain Database Dump Tables Script"
(→Editing dumpTables) |
(→Editing dumpTables) |
||
| Line 7: | Line 7: | ||
==Editing dumpTables== | ==Editing dumpTables== | ||
| − | You need to edit the script to make it work for your system. Edit these values in the script to match your system's values | + | You need to edit the script to make it work for your system. Edit these values in the script to match your system's values. There are three different sections |
| − | + | '''DATABASE CONNECTION AND DUMP OUTPUT LOCATION''' | |
| − | *DBNAMES="''' | + | *DBNAMES="'''[Retain database name]'''" (e.g., retain) |
| − | *HOST="--host='''[localhost or IP/DNS hostname of DB server]'''" ( | + | *HOST="--host='''[localhost or IP/DNS hostname of DB server]'''" (The database server's IP address or DNS hostname if on a separate server from which the script is running. If on the same server, use '''localhost''' ) |
| − | *USER="--user='''root'''" ( | + | *USER="--user='''root'''" (The MySQL superuser, which is typically "root" is most if not all cases; thus, leave this setting as "root") |
| − | *PASSWORD="--password='''[password]'''" ( | + | *PASSWORD="--password='''[password]'''" (The MySQL root user password) |
| − | *BACKUP_DIR="'''[path to destination dump directory]'''" ( | + | *BACKUP_DIR="'''[path to destination dump directory]'''" (The directory to which to save the table files. Be sure to give the destination directory the ownership of mysql:mysql and that it has r/w rights) |
| − | + | '''FILE COMPRESSION OPTIONS''' | |
| + | Enabling either or both of these options disables the easy restore. Rarely would you have a need for the tar option; however, if a customer is transferring their database dump to GWAVA's datacenter or to some other location within their environment, using the GZip option would reduce the size of the file and protect it during transport. You could use a script for copying the GZip files to the new location as they are created so that they can immediately be imported into the new system while the dump process continues to process more tables. | ||
| + | |||
| + | Valid values: yes / no | ||
| + | |||
| + | GZip Individual Tables: | ||
| + | ZIP="no" | ||
| − | The tables will be saved to the file '''__restoreData.sql''' | + | Tar all the individual files into one single .tar file at the end of the job: |
| + | TAR="no" | ||
| + | |||
| + | '''PRE-EXISTING FILE CLEANUP''' | ||
| + | Cleans up files from a previous run of the dump tables script. This is a dangerous setting as you could lose dump files that you man want to have remain. | ||
| + | |||
| + | Valid values: yes / no | ||
| + | |||
| + | CLEANUP="no" | ||
| + | |||
| + | INNODB DUMP OPTIONS | ||
| + | You can change the following InnoDB options. They are optional but know what you're doing. Note that the "'''--single-transaction'''" option is helpful for when Tomcat is left running. If Tomcat is left down, then it isn't necessary. Reference [https://dev.mysql.com/doc/refman/5.5/en/mysqldump.html#option_mysqldump_single-transaction https://dev.mysql.com/doc/refman/5.5/en/mysqldump.html#option_mysqldump_single-transaction]. | ||
| + | |||
| + | OPTIONS="--default-character-set=utf8 --complete-insert --extended-insert --quick --opt --single-transaction" | ||
| + | |||
| + | After editing the settings: | ||
| + | * Save the script | ||
| + | * Give the script file execute permissions ('''chmod +x dumpTables.sh''') | ||
| + | * Make MySQL the owner of the destination dump directory: chown mysql:mysql [path to dump directory] | ||
| + | * Ensure that the directory has read and write rights: chmod +r+w [path to dump directory] | ||
| + | * From the directory containing the script, run the script: '''./dumpTables.sh''' | ||
| + | |||
| + | The instructions for importing all of the tables will be saved to the file '''__restoreData.sql'''; thus, when the dump has been completed, the only file you need to import is the '''_restoreData.sql'''. | ||
==Restoring tables== | ==Restoring tables== | ||
Revision as of 14:04, 8 September 2016
There are occasions that going a regular dump of the database isn't enough when there is an issue. You can do a dump by tables instead.
This script does this for you. It logs into the retain database and dumps each table into a file. The script dumpTables.sh can obtained two ways:
- For Americas support engineers, you can find it in the /mysql directory of the Software Repository.
- For everyone else, click here to download it.
Editing dumpTables
You need to edit the script to make it work for your system. Edit these values in the script to match your system's values. There are three different sections
DATABASE CONNECTION AND DUMP OUTPUT LOCATION
- DBNAMES="[Retain database name]" (e.g., retain)
- HOST="--host=[localhost or IP/DNS hostname of DB server]" (The database server's IP address or DNS hostname if on a separate server from which the script is running. If on the same server, use localhost )
- USER="--user=root" (The MySQL superuser, which is typically "root" is most if not all cases; thus, leave this setting as "root")
- PASSWORD="--password=[password]" (The MySQL root user password)
- BACKUP_DIR="[path to destination dump directory]" (The directory to which to save the table files. Be sure to give the destination directory the ownership of mysql:mysql and that it has r/w rights)
FILE COMPRESSION OPTIONS Enabling either or both of these options disables the easy restore. Rarely would you have a need for the tar option; however, if a customer is transferring their database dump to GWAVA's datacenter or to some other location within their environment, using the GZip option would reduce the size of the file and protect it during transport. You could use a script for copying the GZip files to the new location as they are created so that they can immediately be imported into the new system while the dump process continues to process more tables.
Valid values: yes / no
GZip Individual Tables: ZIP="no"
Tar all the individual files into one single .tar file at the end of the job: TAR="no"
PRE-EXISTING FILE CLEANUP Cleans up files from a previous run of the dump tables script. This is a dangerous setting as you could lose dump files that you man want to have remain.
Valid values: yes / no
CLEANUP="no"
INNODB DUMP OPTIONS You can change the following InnoDB options. They are optional but know what you're doing. Note that the "--single-transaction" option is helpful for when Tomcat is left running. If Tomcat is left down, then it isn't necessary. Reference https://dev.mysql.com/doc/refman/5.5/en/mysqldump.html#option_mysqldump_single-transaction.
OPTIONS="--default-character-set=utf8 --complete-insert --extended-insert --quick --opt --single-transaction"
After editing the settings:
- Save the script
- Give the script file execute permissions (chmod +x dumpTables.sh)
- Make MySQL the owner of the destination dump directory: chown mysql:mysql [path to dump directory]
- Ensure that the directory has read and write rights: chmod +r+w [path to dump directory]
- From the directory containing the script, run the script: ./dumpTables.sh
The instructions for importing all of the tables will be saved to the file __restoreData.sql; thus, when the dump has been completed, the only file you need to import is the _restoreData.sql.
Restoring tables
To Restore a Database:
- gzip -d [gz dump file]
- tar -xf [tar dump file]
- mysql -uroot -p[root pwd] < __restoreData.sql
