Skip site navigation (1)Skip section navigation (2)

FreeBSD Manual Pages


home | help
MYSQLBACKUP(1)			 User Commands			MYSQLBACKUP(1)

       mysqlbackup - creates MySQL backups on a	periodic basis

       mysqlbackup [OPTIONS] [database [database [ ... ] ]]

       mysqlbackup meant to create MySQL databases backup on a periodic	basis.

       -a     Dump  all	 available  databases  except "information_schema" and
	      "performance_schema" databases.

       -u user
	      The MySQL	user name to use when connecting to the	server.

       -h host
	      Connect to host.

       -p password
	      Password to use when connecting to server.  You should note that
	      specifying  a  password on the command line should be considered
	      insecure.	 See the section named "SECURITY".

       -P filename|ask
	      Read the clear password from the file.  The file	must  normally
	      not  be  readable	by "others" and	must contain exactly one line.
	      Password will be prompted	from the command line if  the  special
	      keyword "ask" specified here.

       -x login-path
	      MySQL login-path

       -o option|no
	      Additional  mysqldump  option.   To specify multiple options you
	      should repeat this key for each mysqldump-option.	  The  default
	      options are: --opt --skip-lock-tables --quote-names.  To not use
	      the default options force	"no" option.

       -l days
	      Keep created backups for the specified number of the days.   The
	      default is 5 days.

       -d directory
	      Target  directory	to archive backups.  The default is /var/back-
	      ups (will	be created if need).

       -z xz|pbzip2|bzip2|gzip|7z|no
	      Compress dumps with specified program.  Unless explicitly	set or
	      "no" keyword used, the compressor	is selected in the next	order:
	      if xz(1) compressor found	in $PATH, it will be used.  If it  not
	      found, bzip2(1), gzip(1) and 7z(1) programs will be searched and
	      used if found.  If none found, plain dumps will be created.

       -Z     Pipeline mysqldump to compressor program.	  By  default,	mysql-
	      backup  create  plain  SQL dump for whole	database and call com-
	      pressor program afterwards.  This	help to	make MySQL locktime as
	      small as possible.  If long locktime for huge databases is not a
	      problem but filesystem space usage is - use  this	 key  to  save
	      disk space.

       -F mode
	      Create  files  with  given mode access permissions.  The default
	      mode is 0600.

       -D mode
	      Create directories with given mode access	permissions.  The  de-
	      fault mode is 0700.

       -m path|yes|no
	      Save  my.cnf  config  or specify it alternate path.  Default is:
	      yes, %%DATADIR%%my.cnf.

       -C yes|no|keys
	      Check tables before doing	 backup	 or  use  specified  keys  for
	      mysqlcheck(1)  program  while  perfoming	check.	 Default: yes,
	      keys: --auto-repair --check-only-changed --extended --silent.

       -O yes|no|keys
	      Optimize tables before doing backup or use  specified  keys  for
	      mysqlcheck(1)  program  while perfoming optimization.  Note that
	      not all table engines supports table optimization.  Please refer
	      to  "OPTIMIZE  TABLE  Syntax"  paragraph of MySQL	documentation.
	      Default: yes, keys: --optimize --silent.

       -L lockfile
	      Alternate	  default   path    to	  lockfile    (/var/tmp/mysql-

       -t seconds
	      Timeout  in  seconds  to	expire	existing lockfile.  By default
	      lockfile expires after 90000 seconds.

       -S     Slave mode.  Under this mode mysqlbackup assumes it  is  running
	      on  MySQL	slave.	Then, prior to his work, mysqlbackup stops the
	      slave and	saves "SHOW SLAVE STATUS" output.  After work is done,
	      the  slave  is started up. The output is saved to	"slave-status"

       -I     Ignore errors while dumping database.  mysqlbackup will not stop
	      if  mysqldump(1)	running	 on any	database will return an	error.
	      Excludes -Z because there	is no way to detect which program  has

       -v     Be verbose.

       -V     Print version and	exit.

       -H     Print this help and exit.

       MYSQL_PREFIX  environment  variable  specifies  your MySQL installation
       prefix. All used	MySQL binaries (mysqldump, mysql, mysqlcheck) will  be
       searched	within this prefix at `bin' directory.

       DEBUG_IDENT  environment	variable controls log messages appearance.  In
       verbose mode, by	default, log messages from mysqlbackup	prefixed  with
       timestamp,  process  name and PID.  When	logging	to syslog this may not
       be desired.  To disable prefix in log messages, set  DEBUG_IDENT	 envi-
       ronment to "no".

	      Do nothing, print	help.

       mysqlbackup -av
	      Verbose  backup  all the accessible databases on the local MySQL

       mysqlbackup -z no mysql
	      Backup MySQL system database  without  output  dump  being  com-

       mysqlbackup -a -P ask
	      You are prompted for password to backup all the databases	avail-
	      able under current user.

       mysqlbackup -aS
	      Operate in slave mode.  Save SLAVE STATUS	for  further  replica-
	      tion restore.

       mysqlbackup  exits  on error in mysqldump(1) execution unless -I	option
       given. On exit forced by	some error, mysqlbackup	clean up  its  workdir
       and backup does not created. You	may want to track mysqlbackup's	errors
       on a scheduled jobs to avoid the	situation when no backups  created  at
       all  when  something  prevents mysqlbackup from normal execution. Check
       .done flag-file within destination directory.  Once  backup  succeeded,
       this file is created and	timestamp is written to	it.

       mysqlbackup package comes with a	number of periodic(8) scripts allowing
       to create periodic backups even more transparently. There is number  of
       options	suitable  for  periodic.conf(5)	 that can be used to provide a
       more accurate configuration to run mysqlbackup:

	      (bool) enables or	disables mysqlbackup tool to  run  with	 peri-
	      odic(8).	 The default is	"no". If enabled, mysqlbackup will run
	      by a daily schedule.

	      (str) run	mysqlbackup either daily or weekly or monthly. As men-
	      tioned above - the default schedule is daily.

	      (str)  pass  to mysqlbackup program additional arguments.	By de-
	      fault they are set to -av, which means to	backup all the	avail-
	      able  databases.	Note that this variable	automatically adjusted
	      with -l option based upon	 selected  mysqlbackup_schedule.  With
	      weekly  schedule	backups	are stored for 30 days;	that is, there
	      will be at least four last backups created  on  every  Saturday,
	      for  example.   As for monthly schedule, backups stays untouched
	      for 120 days, allowing to	have minimum four last backups created
	      on the every first day (also, as example)	of last	months.

       If  you need to create backups with a bit different schedule - just add
       command with desired arguments and flags	to your	crontab(1)  to	create
       regular MySQL backups. In order to use mysqlbackup as periodic job - do
       not forget to allow periodic(8) to run mysqlbackup  by  setting	mysql-
       backup_enable to	`yes' in either	/etc/periodic.conf.local or /etc/peri-
       odic.conf file. Check periodic.conf(5) for details.

       You should keep your password in	secure.

       The method of specifying	your MySQL password with  -p  option  must  be
       considered extremely insecure and should	not be used. Your password be-
       comes visible to	system status programs such as ps(1) that may  be  in-
       voked by	other users to display command lines.

       Store  your  password in	an option file.	For example, you can list your
       password	in the [client]	section	of the .my.cnf file in your  home  di-


       To  keep	the password safe, the file should not be accessible to	anyone
       but yourself. To	ensure this, set the file access mode to 400 or	600.

       Users of	MySQL 5.6+ may use "login path"	feature	with -x	option.

       The latest version of this distribution is available on-line from:

       Written by Alexey Degtyarev

       Report bugs to <>

       mysqldump(1), mysqlcheck(1), mysql(1), periodic.conf(5),	periodic(8)

mysqlbackup 2.8			   May 2014			MYSQLBACKUP(1)


Want to link to this manual page? Use this URL:

home | help