You might also backup critical databases or ones that are changed much during slower times of the day so that you don't dimish user interaction. You could then backup larger databases during slower traffic times. Therefore, it might be more useful to stagger backups based on databases, making for possbily several smaller files. This could be take longer to complete the backup and make restoration a bit cumbersome later. Just One Databaseīacking up all of the databases at once with mariadb-dump may result in one large dump file. Notice that the equal-sign is added when the password is given with the long option name. Again, if the backup is to be executed by a shell script, the user's password should be listed: -password= mypwd. The longer option names are easier to follow and to remember. So, the above could and should be entered like this: mariadb-dump -user=admin_backup -password -lock-tables -all-databases > /data/backup/dbs.sql In fact, some are being deprecated and won't be available in the future. The convention is migrating to longer options, not the single letter options. The example given for backing up all database is the short hand version. Finally, the greater-than sign is a shell redirect of the standard output (STDOUT) to the path and file named after it. The -A option specifies that all databases are to be exported. This extended insert option will cause the dump file to be smaller and allow any possible future restores to be executed faster. To bundle INSERT statements together for each table, we've added the -e option. The lock won't be released until the process is finished. The -x option has MariaDB lock all of the tables before performing the backup. If the dump is to be executed by cron by way of a shell script, this option can be changed to -p mypwd, where mypwd is the password-there's no space between the -p and the password. Incidentally, although you might be tempted to just use the root user, you should create a special administrative user as we're using here. The first set of options here ( -u admin_backup -p) tell MariaDB that this utility is to be executed by the user admin_backup and that the user needs to be prompted for a password, which will have to be typed in on the next line when asked. To export all of the databases in MariaDB using mariadb-dump, the following would be entered from the filesystem command-line: mariadb-dump -u admin_backup -p -x -A > /data/backup/dbs.sql If you were to open a dump file generated by mariadb-dump, you would see CREATE TABLE statements and a multitude of INSERT statements, one for each row of data. This data text file (known as a dump file) will contain the SQL statements necessary to reconstruct the databases and data. It works very simply: it retrieves the data and schema from each database and table and builds a data text file outside of MariaDB. Best of all it doesn't require you to shut down MariaDB services to make a backup. It comes with MariaDB, so it costs you nothing more. One of the best utilities to use to make a backup copy of a server's MariaDB's data is mariadb-dump (previously called mysqldump, which still works as a symlink).
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |