Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Next revision		Previous revision
en:linux:mysql:backup [2023/10/24 19:33] – angelegt psycoreen:linux:mysql:backup [2024/01/09 15:09] (current) – Automatic translation 217.113.194.234
Line 1: Line 1:
-====== MySQL backup under Linux ======+{{tag>linux debian mysql backup}} 
 +====== MySQL Backup under Linux ======
  
 ===== Introduction ===== ===== Introduction =====
  
-Nowadays it is impossible to imagine a server without MySQL databases. Information of any kind can be stored, accessed and filtered. For this purpose, MySQL offers a syntax that can be integrated into e.g. PHP without any problems.+MySQL databases have become an integral part of today's servers. Information of all kinds can be stored, retrieved and filtered. MySQL offers a syntax that can be easily integrated into PHP, for example.
  
-To ensure that databases are still available after a system crash, it is necessary to back them up regularly.+To ensure that databases are still available even after a system crash, it is necessary to back them up regularly.
  
 ===== mysqldump ===== ===== mysqldump =====
Line 11: Line 12:
 MySQL comes with a tool called mysqldump. With this tool it is possible to make a backup of all databases. Modern systems work with a password-protected query. For this reason, I will only present this variant here. MySQL comes with a tool called mysqldump. With this tool it is possible to make a backup of all databases. Modern systems work with a password-protected query. For this reason, I will only present this variant here.
  
-If we call mysqldump without parameters, we get the following information on how to use it:+If we call mysqldump without parameters, we get the following instructions for use:
  
 <code bash> <code bash>
Line 27: Line 28:
 all available options are displayed. However, not all of these are relevant for a backup. The most important options for us are: -u -p all available options are displayed. However, not all of these are relevant for a backup. The most important options for us are: -u -p
  
-The option -u specifies the username that has access to the database. The option -p shows a password prompt before executing the command.+The -u option specifies the user name that has access to the database. The -p option displays a password prompt before the command is executed.
  
 ===== Backing up the MySQL database ===== ===== Backing up the MySQL database =====
Line 37: Line 38:
 <code bash>$ mysqldump -u USER -p database USERDATABASE > usr1_db.sql</code> <code bash>$ mysqldump -u USER -p database USERDATABASE > usr1_db.sql</code>
  
-USER corresponds to the user here. The option -p is passed without associated parameters. USERDATABASE corresponds to the actual database. If everything has been entered correctly, we will see a password prompt. Here we enter the password of the user and should now open the file //usr1_db.sql// which contains all database entries.+USER corresponds to the user. The -p option is passed without an associated parameter. USERDATABASE corresponds to the actual database. If everything has been entered correctly, we will see a password prompt. Here we enter the user's password and should now open the file //usr1_db.sql// which contains all database entries.
  
-==== Back up all databases ====+==== Backing up all databases ====
  
-To backup all databases, we need to log in as MySQL root. The following syntax is used, which works similar to the one already described above:+To back up all databases, we must log in as MySQL root. The following syntax is used, which is similar to the one described above:
  
 <code bash>$ mysqldump -u USER -p --all-databases > full_db.sql</code> <code bash>$ mysqldump -u USER -p --all-databases > full_db.sql</code>
  
-===== Restore the MySQL database =====+===== Restoring the MySQL database =====
  
-Importing a dump file is just as easy. For this we use the following syntax:+Importing a dump file is just as easy. We use the following syntax for this:
  
 <code bash>$ mysql -p USERDATABASE -u USER -p < usr1_db.sql</code> <code bash>$ mysql -p USERDATABASE -u USER -p < usr1_db.sql</code>
Line 58: Line 59:
 ===== Automated backup ===== ===== Automated backup =====
  
-To back up the database automatically, you can use the following script, which Jerome Griessmeier provides at his [[http://www.griessmeier.de/linux/mysql_backup.htm#3-1|Webseite]] website:+To back up the database automatically, you can use the following script, which Jerome Griessmeier provides on his [[http://www.griessmeier.de/linux/mysql_backup.htm#3-1|Webseite]] website:
  
 <code bash> <code bash>
Line 103: Line 104:
 <code bash>crontab -e</code> <code bash>crontab -e</code>
  
-Create cronjob:+create cronjob:
  
 <code bash>55 2 * * * root /backup/backup.sh >> /dev/null 2>&1</code> <code bash>55 2 * * * root /backup/backup.sh >> /dev/null 2>&1</code>