0

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.

5
  • Will the backup server be used for anything other than backup? If no, you may want to consider using replication to have data from production sent to the backup server in near real-time. Then you can make SQL dumps from the backup server and not lock up your production system during the backup process.
    – matigo
    Commented Aug 31, 2021 at 14:13
  • LVM is much faster, but harder to setup.
    – Rick James
    Commented Aug 31, 2021 at 17:37
  • What do you want to speed up? Backup or the Load to the playground? Commented Aug 31, 2021 at 20:18
  • How much RAM do you have? How many cores/cpu's on this Windows server? Is a SINGLE server involved? Any SSD or NVME storage devices? Commented Aug 31, 2021 at 20:33
  • Is the data, in particular your large table InnoDB? If you do, innodb_buffer_pool_size = 16M is a very good way of crippling all backup and runtime performance.
    – danblack
    Commented Sep 1, 2021 at 1:53

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.