Automate MySQL Database Backup via Cronjob - Revision history

Docs admin at 17:17, 6 June 2018

2018-06-06T17:17:49Z

← Older revision		Revision as of 13:17, 6 June 2018
Line 22:		Line 22:
	<br>		<br>
	<syntaxhighlight lang="bash">		<syntaxhighlight lang="bash">
	/usr/bin/mysqldump -uDBUSERNAME -pPASSWORD dbname > /home/CPANELUSERNAME/dbname_$date.sql		date=`date -I`; /usr/bin/mysqldump -uDBUSERNAME -pPASSWORD dbname > /home/CPANELUSERNAME/dbname_$date.sql
	</syntaxhighlight>		</syntaxhighlight>
	<br>		<br>
	For Example, cPanel username is example, database name is mydatabase, and database password is mypassword		For Example, cPanel username is example, database name is mydatabase, and database password is mypassword
	<syntaxhighlight lang="bash">		<syntaxhighlight lang="bash">
	/usr/bin/mysqldump -umyusername -pmypassword mydatabase > /home/example/mydatabase_$date.sql		date=`date -I`; /usr/bin/mysqldump -umyusername -pmypassword mydatabase > /home/example/mydatabase_$date.sql
	</syntaxhighlight>		</syntaxhighlight>
	<br>		<br>

Docs admin: /* Set up the Log Rotation cron job */

2018-06-06T17:16:24Z

Set up the Log Rotation cron job

← Older revision		Revision as of 13:16, 6 June 2018
Line 55:		Line 55:

	<syntaxhighlight lang="bash">		<syntaxhighlight lang="bash">
	30 2 * * * /usr/sbin/logrotate -s /home/example/logrotate/my.conf > /dev/null 2>&1		/usr/sbin/logrotate -s /home/example/logrotate/my.conf > /dev/null 2>&1
	</syntaxhighlight>		</syntaxhighlight>
	<br>		<br>
	This will run the rotation at ~~2:30 in~~ the ~~morning~~ daily, using the my.conf file for the instructions.		This will run the rotation at the specified time daily, using the my.conf file for the rotation instructions.<br><br>

	After you click the blue Add New Cron Job button, give the cron a day and check if the backup runs.		After you click the blue Add New Cron Job button, give the cron a day and check if the backup runs.

Docs admin at 17:14, 6 June 2018

2018-06-06T17:14:59Z

← Older revision		Revision as of 13:14, 6 June 2018
Line 45:		Line 45:
	</syntaxhighlight>		</syntaxhighlight>

	This is set to rotate daily, keeping the 3 newest backups. They will be called something similar to mydatabase_20180103.sql.gz, mydatabase_20180102.sql.gz~~, and~~ mydatabase_20180101.sql.gz~~, and the oldest will rotate out when the next one is created.~~		This is set to rotate daily, keeping the 3 newest backups. They will be called something similar to: <br>
	<br>		mydatabase_20180103.sql.gz<br>
			mydatabase_20180102.sql.gz<br>
			mydatabase_20180101.sql.gz<br><br>

	==Set up the Log Rotation cron job==		==Set up the Log Rotation cron job==

Docs admin: /* Create the Log Rotation config file */

2018-06-06T17:13:45Z

Create the Log Rotation config file

← Older revision		Revision as of 13:13, 6 June 2018
Line 45:		Line 45:
	</syntaxhighlight>		</syntaxhighlight>

	This is set to rotate daily, keeping the 3 newest ~~days~~. They will be called something similar to ~~mydatabase~~.sql, ~~mydatabase~~.sql.~~1. mydatabase~~.sql.2, and the oldest will rotate out when the next one is created.		This is set to rotate daily, keeping the 3 newest backups. They will be called something similar to mydatabase_20180103.sql.gz, mydatabase_20180102.sql.gz, and mydatabase_20180101.sql.gz, and the oldest will rotate out when the next one is created.
	<br>		<br>

Docs admin: /* Create the Log Rotation config file */

2018-06-06T17:11:46Z

Create the Log Rotation config file

← Older revision		Revision as of 13:11, 6 June 2018
Line 38:		Line 38:
	You will need to create a logrotate directory under your home directory, and a my,conf fike to enter the directives you will need. Here are the contents of a sample my.conf file:<br>		You will need to create a logrotate directory under your home directory, and a my,conf fike to enter the directives you will need. Here are the contents of a sample my.conf file:<br>
	<syntaxhighlight lang="bash">		<syntaxhighlight lang="bash">
	/home/CPANELUSER/mydatabase.sql {		/home/CPANELUSER/mydatabase*.sql {
	daily		daily
	rotate 3		rotate 3

Docs admin at 17:11, 6 June 2018

2018-06-06T17:11:29Z

← Older revision		Revision as of 13:11, 6 June 2018
Line 22:		Line 22:
	<br>		<br>
	<syntaxhighlight lang="bash">		<syntaxhighlight lang="bash">
	/usr/bin/mysqldump -uDBUSERNAME -pPASSWORD dbname > /home/CPANELUSERNAME/~~dbname~~.sql		/usr/bin/mysqldump -uDBUSERNAME -pPASSWORD dbname > /home/CPANELUSERNAME/dbname_$date.sql
	</syntaxhighlight>		</syntaxhighlight>
	<br>		<br>
	For Example, cPanel username is example, database name is mydatabase, and database password is mypassword		For Example, cPanel username is example, database name is mydatabase, and database password is mypassword
	<syntaxhighlight lang="bash">		<syntaxhighlight lang="bash">
	~~date=`date -I`;~~ /usr/bin/mysqldump -umyusername -pmypassword mydatabase > /home/example/mydatabase_$date.sql		/usr/bin/mysqldump -umyusername -pmypassword mydatabase > /home/example/mydatabase_$date.sql
	</syntaxhighlight>		</syntaxhighlight>
	<br>		<br>
	This will create a sql file in your /home/cpanelusername directory with a name of: ~~mydatabase1~~.sql		This will create a sql file in your /home/cpanelusername directory with a name of: mydatabase_20180101.sql
	<br><br>		<br><br>
	6) Click the blue "Add New Cron Job" button when done.		6) Click the blue "Add New Cron Job" button when done.

Docs admin: /* Set up the Log Rotation cron job */

2018-06-06T17:09:56Z

Set up the Log Rotation cron job

← Older revision		Revision as of 13:09, 6 June 2018
Line 49:		Line 49:

	==Set up the Log Rotation cron job==		==Set up the Log Rotation cron job==

			This will be similar to Step 1, where you set up the mysql database backup. The command to enter would be similar to:<br>

			<syntaxhighlight lang="bash">
			30 2 * * * /usr/sbin/logrotate -s /home/example/logrotate/my.conf > /dev/null 2>&1
			</syntaxhighlight>
			<br>
			This will run the rotation at 2:30 in the morning daily, using the my.conf file for the instructions.

			After you click the blue Add New Cron Job button, give the cron a day and check if the backup runs.

Docs admin: /* Create the Log Rotation config file */

2018-06-06T17:04:15Z

Create the Log Rotation config file

← Older revision		Revision as of 13:04, 6 June 2018
Line 45:		Line 45:
	</syntaxhighlight>		</syntaxhighlight>

	This is set to rotate daily, keeping the 3 newest days. They will be called something similar to mydatabase.sql, mydatabase.sql.1. mydatabase.sql.2, and the oldest ~~rill~~ rotate out when the next one is created.		This is set to rotate daily, keeping the 3 newest days. They will be called something similar to mydatabase.sql, mydatabase.sql.1. mydatabase.sql.2, and the oldest will rotate out when the next one is created.
	<br>		<br>

	==Set up the Log Rotation cron job==		==Set up the Log Rotation cron job==

Docs admin at 17:03, 6 June 2018

2018-06-06T17:03:52Z

← Older revision		Revision as of 13:03, 6 June 2018
Line 22:		Line 22:
	<br>		<br>
	<syntaxhighlight lang="bash">		<syntaxhighlight lang="bash">
	~~date=`date -I`;~~ /usr/bin/mysqldump -uDBUSERNAME -pPASSWORD dbname > /home/CPANELUSERNAME/dbname~~$date~~.sql		/usr/bin/mysqldump -uDBUSERNAME -pPASSWORD dbname > /home/CPANELUSERNAME/dbname.sql
	</syntaxhighlight>		</syntaxhighlight>
	<br>		<br>
Line 30:		Line 30:
	</syntaxhighlight>		</syntaxhighlight>
	<br>		<br>
	This will create a sql file in your /home/cpanelusername directory with a name ~~similar to mydatabase_20180101~~.sql.		This will create a sql file in your /home/cpanelusername directory with a name of: mydatabase1.sql
	<br><br>		<br><br>
	6) Click the blue "Add New Cron Job" button when done.		6) Click the blue "Add New Cron Job" button when done.
Line 36:		Line 36:
	==Create the Log Rotation config file==		==Create the Log Rotation config file==

	You will need to create a logrotate directory under your home directory to enter the directives you will need. Here is a sample ~~config~~:<br>		You will need to create a logrotate directory under your home directory, and a my,conf fike to enter the directives you will need. Here are the contents of a sample my.conf file:<br>
	<syntaxhighlight lang="bash">		<syntaxhighlight lang="bash">
	/home/CPANELUSER/~~logrotate/my~~.~~conf~~ {		/home/CPANELUSER/mydatabase.sql {
	daily		daily
	rotate 3		rotate 3
Line 44:		Line 44:
	}		}
	</syntaxhighlight>		</syntaxhighlight>

			This is set to rotate daily, keeping the 3 newest days. They will be called something similar to mydatabase.sql, mydatabase.sql.1. mydatabase.sql.2, and the oldest rill rotate out when the next one is created.
			<br>

	==Set up the Log Rotation cron job==		==Set up the Log Rotation cron job==

Docs admin: /* Create the Log Rotation config file */

2018-06-06T16:46:27Z

Create the Log Rotation config file

← Older revision		Revision as of 12:46, 6 June 2018
Line 42:		Line 42:
	rotate 3		rotate 3
	compress		compress
	~~delaycompress~~
	}		}
	</syntaxhighlight>		</syntaxhighlight>


	==Set up the Log Rotation cron job==		==Set up the Log Rotation cron job==