So I'm going to make a backup system for my database and wanted to know if there was something specific I have to know before doing too much. I've read that I mught have to change some database settings first?
# Here follows entries for some specific programs
# The MySQL server
[mysqld]
port= 3306
socket = "C:/xampp/mysql/mysql.sock"
basedir = "C:/xampp/mysql"
tmpdir = "C:/xampp/tmp"
datadir = "C:/xampp/mysql/data"
pid_file = "mysql.pid"
# enable-named-pipe
key_buffer = 16M
max_allowed_packet = 256M
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
log_error = "mysql_error.log"
# Comment the following if you are using InnoDB tables
#skip-innodb
innodb_data_home_dir = "C:/xampp/mysql/data"
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = "C:/xampp/mysql/data"
#innodb_log_arch_dir = "C:/xampp/mysql/data"
## You can set .._buffer_pool_size up to 50 - 80 %
## of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 16M
innodb_additional_mem_pool_size = 2M
## Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 128M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
## UTF 8 Settings
#init-connect=\'SET NAMES utf8\'
#collation_server=utf8_unicode_ci
#character_set_server=utf8
#skip-character-set-client-handshake
#character_sets-dir="C:/xampp/mysql/share/charsets"
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
[isamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[myisamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
For making the backup I thought about making the MySQLDump file at followed, and run it every 24 hour
set -e
bin/mysqldump.exe -uNameOnUser -pPassWord --single-transaction --routines --triggers --host HostName --databases TestBasePlayground> backups/TestBasePlaygroundDump.sql
bin/mysqldump.exe -uNameOnUser -pPassWord --single-transaction --routines --triggers --host HostName --databases TestBase> backups/TestBaseDump.sql
git commit -am "Backup: $(date)"
git push origin HEAD:master
And just to make my playground look like the productions data I wanted to load this data into my playground database like this:
mysql -hHostName -uNameOnUser -pPassWord TestBasePlayground< backups/TestBaseDump.sql
I've not set it all up yet, just tested it on CMD-command, and it works. But is this the right way to do it for a 10GB database? Because it takes forever to take a backup and load it to the playground, and I wanted to know if there is any tricks to speed this up.
One table in my database can be 4Gb+ just so you know.
innodb_buffer_pool_size = 16M
is a very good way of crippling all backup and runtime performance.