PHP scheduled backup MySQL and mysqldump syntax parameters detailed

First, let's introduce several common operation examples of the MySQL backup command mysqldump:

1. mysqldump backup

Export only the table structure

d:/PHP/xampp/mysql/bin/mysqldump -h127.0.0.1 -P3306 -uroot -p123456 snsgou_sns_test --no-data --default_character-set=utf8 > d:/Python/data/snsgou_sns_test_table.sql

mysqldump only exports data

d:/PHP/xampp/mysql/bin/mysqldump -h127.0.0.1 -P3306 -uroot -p123456 snsgou_sns_test --no-create-info --default_character-set=utf8 > d:/Python/data/snsgou_sns_test_data.sql

mysqldump export all (including table structure and data)

d:/PHP/xampp/mysql/bin/mysqldump -h127.0.0.1 -P3306 -uroot -p123456 snsgou_sns_test --default_character-set=utf8 > d:/Python/data/snsgou_sns_test.sql

2. mysqldump restore (Note: --force parameter means that execution will continue if an error occurs)

d:/PHP/xampp/mysql/bin/mysql -h127.0.0.1 -P3306 -uroot -p123456 snsgou_sns_test --force < d:/Python/data/snsgou_sns_table.sql 

d:/PHP/xampp/mysql/bin/mysql -h127.0.0.1 -P3306 -uroot -p123456 snsgou_sns_test --force < d:/Python/data/snsgou_sns_data.sql

/usr/local/mysql/bin/mysql -h127.0.0.1 -P3306 -uroot -p'sg$123456' snsgou_sns_test --force < /root/2/snsgou_sns_test.sql

Note: mysqldump can only be used for backup and export, but to restore, you need to use the mysql command.

Based on the above knowledge points, a PHP script for scheduled backup of MySQL database is compiled:

Configuration file: db.inc.jb51.net.php

<?php
/**
 * Database configuration file */
$dbs = array(
  array(
    'host' => '127.0.0.1',
    'port' => 6603,
    'user' => 'root',
    'pass' => '123456',
    'dbname' => 'xxxyyy',
    'charset' => 'utf8'
  ),
  array(
    'host' => '127.0.0.1',
    'port' => 6603,
    'user' => 'root',
    'pass' => '123456',
    'dbname' => 'yyyzzz',
    'charset' => 'utf8'
  ),
);
 
$backupPath = 'F:/mysql_bak/' . date('Y-m') . '/' . date('d');
$oldPath = 'F:/mysql_bak/' . (intval(date('Ym')) - 1);
 
// Note: If there are spaces in the path, you need to use double quotes $cmd = '"D:/Program Files/MySQL/MySQL Server 5.5/bin/mysqldump"';
 
// Export all $cmd .= " -h%s -P%s -u%s -p%s %s --default_character-set=%s > %s";
 
//Export only the table structure//$cmd .= " -h%s -P%s -u%s -p%s %s --no-data --default_character-set=%s > %s";
 
// Export data only //$cmd .= " -h%s -P%s -u%s -p%s %s --no-create-info --default_character-set=%s > %s";

Backup file: backup_db.php

<?php
/**
 * Backup database */
 
set_time_limit(0);
define('ROOT_PATH', dirname(__FILE__));
 
if (!@include ROOT_PATH . '/db.inc.jb51.net.php') {
  echo 'args error';
  //write('args error');
  exit(1);
}
 
// Create a new backup directory new_mkdir($backupPath);
 
// Delete data from one month ago new_rmdir($oldPath);
 
 
//Batch backup database foreach ($dbs as $db) {
  $cmd2 = '';
  if (isset($db['name'])) {
    $fileName = $backupPath . '/' . $db['name'] . '_' . date('Ymd') . '_' . time() . '.sql';
    $cmd2 = sprintf($cmd, $db['host'], $db['port'], $db['user'], $db['pass'], $db['name'], $db['charset'], $fileName);
    //write($cmd2);
    try {
      exec($cmd2);
    } catch (Exception $e) {
      // Write error log (omitted)
      echo $e->getMessage();
    }
  }
}
 
 
/**
 * Create Folder *
 * @param string $path folder path* @param int $mode access rights* @param boolean $recursive whether to create recursively*/
function new_mkdir($path = '', $mode = 0777, $recursive = true) {
  clearstatcache();
  if (!is_dir($path)) {
    mkdir($path, $mode, $recursive);
    chmod($path, $mode);
    $ret = @touch($path . '/index.html');
    @chmod($path . '/index.html', 0777);
    return $ret;
  }
 
  return true;
}
 
/**
 * Clear/Delete Folders*
 * @param string $dirname folder path * @param bool $self whether to delete the current folder * @return bool
 */
function new_rmdir($dirname = '', $self = true) {
  if (!file_exists($dirname)) {
    return false;
  }
  if (is_file($dirname) || is_link($dirname)) {
    return unlink($dirname);
  }
  $dir = dir($dirname);
  if ($dir) {
    while (false !== $entry = $dir->read()) {
      if ($entry == '.' || $entry == '..') {
        continue;
      }
      new_rmdir($dirname . '/' . $entry);
    }
  }
  $dir->close();
  $self && rmdir($dirname);
}
 
/**
 * Write file *
 * @param string $filename file name * @param string $text text string to be written * @param string $openmod text writing mode ('w': overwrite, 'a': text append)
 * @return boolean
 */
function write_file($filename = '', $text = '', $openmod = 'w') {
  if (@$fp = fopen($filename, $openmod)) {
    flock($fp, 2);
    fwrite($fp, $text);
    fclose($fp);
    return true;
  } else {
    return false;
  }
}
 
/**
 * Write objects (including numbers, strings, arrays)
 *
 * @param string $text The text string to be written * @param string $type Text writing type ('w': overwrite, 'a': text append)
 */
function write($text = '', $type = 'a') {
  if (!is_dir('d:/PHP/xampp/htdocs')) {
    return false;
  }
  $filename = 'd:/PHP/xampp/htdocs/write.txt';
 
  $text = "\r\n++++++++++++++++++++++++++++++++++++++++++\r\n"
    . date('Ymd H:i:s') . "\r\n"
    .print_r($text, true);
  write_file($filename, $text, $type);
}

# Scheduled task execution:

d:/PHP/xampp/php/php.exe D:/PHP/xampp/htdocs/BackupDb_git/backup_db.php snsgou.com

# Note: First, make sure the above command can be successfully executed in the cmd command!

To configure scheduled tasks under Windows, please refer to: Windows Server 2008 Scheduled Task Configuration (Task Scheduler) Execute BAT

In "New Action",

"Program or script": select the PHP parser, such as d:/PHP/xampp/php/php.exe,
"Add parameters (optional)": D:/PHP/xampp/htdocs/BackupDb_git/backup_db.php jb51.net

For configuring scheduled tasks in Linux, please refer to: Linux scheduled task Crontab command detailed explanation

Appendix: Complete list of mysqldump parameters

Parameter Description

--all-databases , -A

Export the entire database.

mysqldump -uroot -p --all-databases

--all-tablespaces , -Y

Export all tablespaces.

mysqldump -uroot -p --all-databases --all-tablespaces

--no-tablespaces , -y

Do not export any tablespace information.

mysqldump -uroot -p --all-databases --no-tablespaces

--add-drop-database

Add a drop database statement before each database is created.

mysqldump -uroot -p --all-databases --add-drop-database

--add-drop-table

Add a drop table statement before creating each table. (Enabled by default, use --skip-add-drop-table to disable the option)

mysqldump -uroot -p --all-databases (drop statement added by default)

mysqldump -uroot -p --all-databases –skip-add-drop-table (cancel the drop statement)

--add-locks

Add LOCK TABLES before each table export and UNLOCK TABLE afterwards. (Enabled by default, use --skip-add-locks to disable it)

mysqldump -uroot -p --all-databases (LOCK statement is added by default)

mysqldump -uroot -p --all-databases –skip-add-locks (cancel the LOCK statement)

--allow-keywords

Allows creation of column names that are keywords. This is done by prefixing each column name with the table name.

mysqldump -uroot -p --all-databases --allow-keywords

--apply-slave-statements

Add 'STOP SLAVE' before 'CHANGE MASTER' and add 'START SLAVE' at the end of the export.

mysqldump -uroot -p --all-databases --apply-slave-statements

--character-sets-dir

Directory for character set files

mysqldump -uroot -p --all-databases --character-sets-dir=/usr/local/mysql/share/mysql/charsets

--comments

Additional annotation information. The default is on, you can disable it with --skip-comments

mysqldump -uroot -p --all-databases (default record comments)

mysqldump -uroot -p --all-databases --skip-comments (uncomment)

--compatible

The exported data will be compatible with other databases or older versions of MySQL. The value can be ansi, mysql323, mysql40, postgresql, oracle, mssql, db2, maxdb, no_key_options, no_tables_options, no_field_options, etc.

To use several values, separate them with commas. It does not guarantee full compatibility, but tries to be compatible.

mysqldump -uroot -p --all-databases --compatible=ansi

--compact

Export less output information (useful for debugging). Remove comments, headers, and footers. You can use the following options: --skip-add-drop-table --skip-add-locks --skip-comments --skip-disable-keys

mysqldump -uroot -p --all-databases --compact

--complete-insert, -c

Use a complete insert statement (including column names). Doing so can improve insertion efficiency, but may be affected by the max_allowed_packet parameter and cause insertion failure.

mysqldump -uroot -p --all-databases --complete-insert

--compress, -C

Enable compression for all messages sent between the client and server

mysqldump -uroot -p --all-databases --compress

--create-options, -a

Include all MySQL-specific options in the CREATE TABLE statement. (Open by default)

mysqldump -uroot -p --all-databases

--databases, -B

Export several databases. All name parameters following this parameter are considered database names.

mysqldump -uroot -p --databases test mysql

--debug

Output debug information for debugging. The default value is: d:t:o,/tmp/mysqldump.trace

mysqldump -uroot -p --all-databases --debug

mysqldump -uroot -p --all-databases --debug="d:t:o,/tmp/debug.trace"

--debug-check

Check memory and open file usage and exit.

mysqldump -uroot -p --all-databases --debug-check

--debug-info

Output debugging information and exit

mysqldump -uroot -p --all-databases --debug-info

--default-character-set

Set the default character set, the default value is utf8

mysqldump -uroot -p --all-databases --default-character-set=latin1

--delayed-insert

Export data using delayed insert (INSERT DELAYED)

mysqldump -uroot -p --all-databases --delayed-insert

--delete-master-logs

Delete logs after master backup. This parameter will automatically activate --master-data.

mysqldump -uroot -p --all-databases --delete-master-logs

--disable-keys

For each table, reference the INSERT statements with /*!40000 ALTER TABLE tbl_name DISABLE KEYS */; and /*!40000 ALTER TABLE tbl_name ENABLE KEYS */; statements. This allows for faster import of the dump file because it creates the index after all rows have been inserted. This option is only applicable to MyISAM tables and is turned on by default.

mysqldump -uroot -p --all-databases

--dump-slave

This option will cause the master's binlog position and file name to be appended to the file where the data is exported. When set to 1, the CHANGE MASTER command will be output to the data file; when set to 2, an explanation will be added before the command. This option will turn on --lock-all-tables unless --single-transaction is specified. This option automatically turns off the --lock-tables option. The default value is 0.

mysqldump -uroot -p --all-databases --dump-slave=1

mysqldump -uroot -p --all-databases --dump-slave=2

--events, -E

Export events.

mysqldump -uroot -p --all-databases --events

--extended-insert, -e

Use INSERT syntax with multiple VALUES columns. This makes the exported file smaller and speeds up the import process. This is on by default, use --skip-extended-insert to disable it.

mysqldump -uroot -p --all-databases

mysqldump -uroot -p --all-databases--skip-extended-insert (cancel option)

--fields-terminated-by

Ignore the given field in the export file. Used with the --tab option. Cannot be used with the --databases and --all-databases options.

mysqldump -uroot -p test test --tab="/home/mysql" --fields-terminated-by="#"

--fields-enclosed-by

Each field in the output file is wrapped with the given character. Used with the --tab option. Cannot be used with the --databases and --all-databases options.

mysqldump -uroot -p test test --tab="/home/mysql" --fields-enclosed-by="#"

--fields-optionally-enclosed-by

The fields in the output file are optionally wrapped with the given character. Used with the --tab option. Cannot be used with the --databases and --all-databases options.

mysqldump -uroot -p test test --tab="/home/mysql" --fields-enclosed-by="#" --fields-optionally-enclosed-by ="#"

--fields-escaped-by

The given characters are ignored for each field in the output file. Used with the --tab option. Cannot be used with the --databases and --all-databases options.

mysqldump -uroot -p mysql user --tab="/home/mysql" --fields-escaped-by="#"

--flush-logs

Flush the log before starting the export.

Please note: If you export multiple databases at once (using option --databases or --all-databases), the logs will be flushed database by database. Except when using --lock-all-tables or --master-data. In this case, the log will be flushed once and all corresponding tables will be locked at the same time. Therefore, if you plan to export and flush logs at the same time, you should use --lock-all-tables or --master-data and --flush-logs.

mysqldump -uroot -p --all-databases --flush-logs

--flush-privileges

After exporting the mysql database, issue a FLUSH PRIVILEGES statement. For proper recovery, this option should be used whenever you export the mysql database or depend on mysql database data.

mysqldump -uroot -p --all-databases --flush-privileges

--force

Ignore SQL errors that occur during the export process.

mysqldump -uroot -p --all-databases --force

--help

Display help information and exit.

mysqldump --help

--hex-blob

Exports binary string fields using hexadecimal format. This option must be used if there is binary data. The affected field types are BINARY, VARBINARY, and BLOB.

mysqldump -uroot -p --all-databases --hex-blob

--host, -h

Host information to be exported

mysqldump -uroot -p --host=localhost --all-databases

--ignore-table

Do not export the specified table. To specify multiple tables to ignore, repeat the command multiple times, one table at a time. Each table must specify both the database and table name. For example: --ignore-table=database.table1 --ignore-table=database.table2 …

mysqldump -uroot -p --host=localhost --all-databases --ignore-table=mysql.user

--include-master-host-port

Add 'MASTER_HOST=<host>, MASTER_PORT=<port>' to the 'CHANGE MASTER TO..' statement generated by --dump-slave

mysqldump -uroot -p --host=localhost --all-databases --include-master-host-port

--insert-ignore

Use the INSERT IGNORE statement when inserting rows.

mysqldump -uroot -p --host=localhost --all-databases --insert-ignore

--lines-terminated-by

Each line of the output file is delimited by the given string. Used with the --tab option. Cannot be used with the --databases and --all-databases options.

mysqldump -uroot -p --host=localhost test test --tab="/tmp/mysql" --lines-terminated-by="##"

--lock-all-tables, -x

The commit request locks all tables in all databases to ensure data consistency. This is a global read lock, and automatically turns off the --single-transaction and --lock-tables options.

mysqldump -uroot -p --host=localhost --all-databases --lock-all-tables

--lock-tables, -l

Before starting the export, lock all tables. Lock the table with READ LOCAL to allow parallel inserts into MyISAM tables. For tables that support transactions such as InnoDB and BDB, --single-transaction is a better choice because it does not require locking the table at all.

Please note that when exporting multiple databases, --lock-tables locks the tables for each database separately. Therefore, this option cannot guarantee the logical consistency of the tables in the export file across databases. The export status of different database tables can be completely different.

mysqldump -uroot -p --host=localhost --all-databases --lock-tables

--log-error

Append warning and error messages to the given file

mysqldump -uroot -p --host=localhost --all-databases --log-error=/tmp/mysqldump_error_log.err

--master-data

This option appends the binlog location and file name to the output file. If it is 1, the CHANGE MASTER command will be output; if it is 2, comment information will be added before the output CHANGE MASTER command. This option turns on the --lock-all-tables option unless --single-transaction is also specified (in which case a global read lock is acquired briefly at the start of the export; see the --single-transaction option below for more information). This option automatically turns off the --lock-tables option.

mysqldump -uroot -p --host=localhost --all-databases --master-data=1;

mysqldump -uroot -p --host=localhost --all-databases --master-data=2;

--max_allowed_packet

The maximum packet length that the server can send and receive.

mysqldump -uroot -p --host=localhost --all-databases --max_allowed_packet=10240

--net_buffer_length

Buffer size for TCP/IP and socket connections.

mysqldump -uroot -p --host=localhost --all-databases --net_buffer_length=1024

--no-autocommit

Wrap the table with autocommit/commit statements.

mysqldump -uroot -p --host=localhost --all-databases --no-autocommit

--no-create-db, -n

Export only the data without adding a CREATE DATABASE statement.

mysqldump -uroot -p --host=localhost --all-databases --no-create-db

--no-create-info, -t

Export only the data without adding CREATE TABLE statements.

mysqldump -uroot -p --host=localhost --all-databases --no-create-info

--no-data, -d

Do not export any data, only the database table structure.

mysqldump -uroot -p --host=localhost --all-databases --no-data

--no-set-names, -N

Equivalent to --skip-set-charset

mysqldump -uroot -p --host=localhost --all-databases --no-set-names

--opt

Equivalent to --add-drop-table, --add-locks, --create-options, --quick, --extended-insert, --lock-tables, --set-charset, --disable-keys This option is enabled by default and can be disabled with --skip-opt.

mysqldump -uroot -p --host=localhost --all-databases --opt

--order-by-primary

If a primary key exists, or the first unique key, sort the records of each table. This works when exporting a MyISAM table to an InnoDB table, but it makes the export take a long time.

mysqldump -uroot -p --host=localhost --all-databases --order-by-primary

--password, -p

Password for connecting to the database

--pipe (available on Windows)

Connect to mysql using named pipes

mysqldump -uroot -p --host=localhost --all-databases --pipe

--port, -P

Database connection port number

--protocol

The connection protocols used include: tcp, socket, pipe, memory.

mysqldump -uroot -p --host=localhost --all-databases --protocol=tcp

--quick, -q

Do not buffer queries, export directly to standard output. This is on by default, use --skip-quick to disable it.

mysqldump -uroot -p --host=localhost --all-databases

mysqldump -uroot -p --host=localhost --all-databases --skip-quick

--quote-names,-Q

Use quotes (`) to enclose table and column names. This is on by default, use --skip-quote-names to disable it.

mysqldump -uroot -p --host=localhost --all-databases

mysqldump -uroot -p --host=localhost --all-databases --skip-quote-names

--replace

Use REPLACE INTO instead of INSERT INTO.

mysqldump -uroot -p --host=localhost --all-databases --replace

--result-file, -r

Output directly to the specified file. This option should be used on systems that use carriage return and line feed pairs (\\r\\n) for line breaking (eg DOS, Windows). This option ensures that only one row is used.

mysqldump -uroot -p --host=localhost --all-databases --result-file=/tmp/mysqldump_result_file.txt

--routines, -R

Export stored procedures and custom functions.

mysqldump -uroot -p --host=localhost --all-databases --routines

--set-charset

Add 'SET NAMES default_character_set' to output files. This is on by default, use the --skip-set-charset option to turn it off.

mysqldump -uroot -p --host=localhost --all-databases

mysqldump -uroot -p --host=localhost --all-databases --skip-set-charset

--single-transaction

This option submits a BEGIN SQL statement before exporting data. BEGIN will not block any application and can ensure the consistency of the database during export. It only works with multiversion storage engines, InnoDB only. This option is mutually exclusive with the --lock-tables option, since LOCK TABLES causes any pending transactions to be implicitly committed. To export large tables, use the --quick option.

mysqldump -uroot -p --host=localhost --all-databases --single-transaction

--dump-date

Adds export time to output file. This is on by default, use the --skip-dump-date option to turn it off.

mysqldump -uroot -p --host=localhost --all-databases

mysqldump -uroot -p --host=localhost --all-databases --skip-dump-date

--skip-opt

Disable the --opt option.

mysqldump -uroot -p --host=localhost --all-databases --skip-opt

--socket,-S

Specify the socket file location for connecting to MySQL. The default path is /tmp/mysql.sock

mysqldump -uroot -p --host=localhost --all-databases --socket=/tmp/mysqld.sock

--tab,-T

Creates tab-delimited text files for each table at the given path. Note: This only works if mysqldump and the mysqld server are running on the same machine.

mysqldump -uroot -p --host=localhost test test --tab="/home/mysql"

--tables

Override the --databases (-B) parameter to specify the table name to be exported.

mysqldump -uroot -p --host=localhost --databases test --tables test

--triggers

Export triggers. This option is enabled by default, disable it with --skip-triggers.

mysqldump -uroot -p --host=localhost --all-databases --triggers

--tz-utc

Set the time zone TIME_ZONE='+00:00' at the top of the export to ensure correctness of TIMESTAMP data exported in different time zones or when data is moved to other time zones.

mysqldump -uroot -p --host=localhost --all-databases --tz-utc

--user, -u

Specify the user name for the connection.

--verbose, --v

Output various platform information.

--version, -V

Output mysqldump version information and exit

--where, -w

Dump only the records selected by the given WHERE condition. Note that if a condition contains spaces or characters specific to the command interpreter, be sure to quote the condition.

mysqldump -uroot -p --host=localhost --all-databases --where="user='root'"

--xml, -X

Export to XML format.

mysqldump -uroot -p --host=localhost --all-databases --xml

--plugin_dir

The directory of client plugins, used for compatibility with different plugin versions.

mysqldump -uroot -p --host=localhost --all-databases --plugin_dir="/usr/local/lib/plugin"

--default_auth

The client plugin uses the default permissions.

mysqldump -uroot -p --host=localhost --all-databases --default-auth="/usr/local/lib/plugin/<PLUGIN>"

The above is to introduce PHP's use of mysqldump command to schedule MySQL backup and mysqldump syntax parameters as well as PHP example code for scheduled backup. I hope it will be useful to everyone.