The following example backs up all the databases on the MySQL server: mysqldump -u root -adm#1$$$ -all-databases > c:backupsall-databases.sql Then to import them to another database server you would run something like: mysql db1_and_2.sql In that case, you could run something like: mysqldump -routines -no-create-info -no-data -no-create-db -skip-opt > dumpfilename.sql It can sometimes be useful to import stored procedures and triggers to another database that already contains the data. This means that if you want to include the triggers and stored procedures you only need to add the –routines command line parameter: mysqldump - routines > dumpfilename.sql There are two mysqldump parameters that control this behavior: By default, mysqldump will backup all the triggers but NOT the stored procedures/functions. You may also want the stored procedures, functions, and triggers, unless you don’t mind spending weeks to rebuild them. # mysqldump -u root -p > dumpfilename.sql Here’s the basic syntax for both: backup: To restore the database, simply execute the *.sql file on the destination database. It usually creates *.sql files with DROP table, CREATE table and INSERT into sql-statements of the source database, but it can also be used to generate files in CSV (or other delimited text) or XML format. The mysqldump client is a free command line utility that comes with MySQL to perform database backups and restores. Now, with the introduction of PowerShell, database backups can be more automated than ever. To this end, tools like mysqldump are indispensable. That’s why having an established reliable backup and restore process is so important. All in all, it can be a time intensive endeavor. Equally important is the maintenance of the backup files and practicing of the restoration procedure. Ability to back up and restore MySQL master and slave script.One of the Database Administrator (DBA)’s most important duties is performing regular scheduled database backups.Ability to send an email with the error report when an error occurs ( Free SMTP Server).Ability to back up and restore all field types of MySQL (BLOB, TEXT, VARCHAR, DATE, DECIMAL, etc.).Ability to back up and restore all store engines of MySQL (InnoDB, MyISAM, BDB, etc.).Ability to back up and restore all character sets of MySQL (Unicode, UTF8, etc.).Ability to back up and restore all items of MySQL (Tablespaces, Databases, Tables, Views, Triggers, Stored Procedures, Functions, Events, Indexes, Primary Keys, Foreign Keys, etc.).Ability to record the MySQL backup files history for recovery.Ability to automatically delete the expired MySQL backup files.Ability to select MySQL databases from the SQL backup files when recovery ( Reverse Parse).Ability to back up and restore the huge MySQL databases ( larger than 100GB).Ability to back up the newly-created MySQL databases.Ability to connect MySQL server by TCP/IP, SSL, SSH, Compression, Shared-Memory, Named Pipe, Socket protocols.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |