|Paolo Asperti 56ad6595a3||6 months ago|
|.drone.yml||6 months ago|
|Dockerfile||2 years ago|
|LICENSE||2 years ago|
|README.md||6 months ago|
|automysqlbackup||2 years ago|
|custom.cnf||2 years ago|
|docker-entrypoint.sh||2 years ago|
|healthcheck.sh||2 years ago|
|maria-include.cnf||2 years ago|
|mysql.cnf||2 years ago|
|sudoers||2 years ago|
Setup a mariadb slave server with automysqlbackup
This is used to customize slave server ID. The default value is 33. You should use an unique ID for each server.
When set to 1, the slave server is set as readonly. This is the default. If you set this variable to something different, the database will be read/write.
This is used to specify a single database to backup. It is generally advised to set this variable to the database name you want to backup.
How to setup a mysql slave for backup
Configure the master node
Examine all mariadb config files, they're usually located in /etc/mysql/. You must have these instructions:
[mysqld] server_id = 1 log_bin log-bin = binlog max-binlog-size = 500M binlog-do-db = my-db-name bind-address = 0.0.0.0
You must have an unique server_id and binlog enabled (the max-binlog-size is purely an example). The bind-address is here to make sure your server is reachable from the backup node. Maybe you can consider a firewall rule if your db shouldn't be reached from outside. The binlog-do-db is used to specify which database you want to be used with binlog; you can have multiple binlog-do-db lines if you need to replicate more than one db.
After these modifications, restart mariadb.
Create a replication user
Execute this statement on the master node:
grant replication slave on *.* to firstname.lastname@example.org identified by 'aRandomPassword'; flush privileges;
Replace repluser, backupslave.host, aRandomPassword with something more meaningful. Please take note that in replication you cannot use a password longer than 32 characters.
Start the slave server
We use docker-compose here, but you can do the same with plain docker. Here's a sample docker-compose.yaml file:
version: "3" services: backup-slave: image: docker.asperti.com/paspo/mariadb-backup-slave restart: always volumes: - "./mysql/:/var/lib/mysql" - "./backup/:/var/lib/automysqlbackup" environment: - MYSQL_ROOT_PASSWORD=aSecurePassword - SERVER_ID=3 - READONLY=0 - REPLICATE_DO_DB=aWordpressDatabase
Start the container:
docker-compose up -d
Create the database:
docker exec my-container sh -c 'echo "create database aWordpressDatabase;" | exec mysql -uroot -p"$MYSQL_ROOT_PASSWORD"'
Freeze the master
To obtain a consistant slave, you first have to freeze the master, and then transfer the data. Execute this on the master:
FLUSH TABLES WITH READ LOCK; show master status;
Keep this session running!!! If you close it, it will release the lock.
Take note of file and position, we'll need these informations later.
Import initial data
We have two ways to copy the data from the master to the slave: we'll call these "live" and "classic". The classic way is the usual mysqldump + file transfer + import. The live way is dump + import at the same time, without an intermediate file. The live way is preferred when you have small databases (less than 5Gb) and a good bandwidth (100+ Mbit) available between the master and the slave. The classic way is preferred when you have a big database because it locks the database for less time.
Import initial data (live version)
Now, we need to import the backup from the master, so as we started the slave as readwrite, we can now do this on the slave:
docker exec my-container sh -c 'mysqldump -C --lock-tables=false --quick -u repluser -h master.host --password="aRandomPassword" aWordpressDatabase | mysql -u root -p"$MYSQL_ROOT_PASSWORD" aWordpressDatabase'
Replace my-container, repluser, master.host, aRandomPassword, aWordpressDatabase with the correct values.
Now that the slave has got some data, we can release the lock in the master:
Import initial data (classic version)
In the master we run mysqldump in a shell:
mysqldump -u root -p aWordpressDatabase | gzip > thedump.sql.gz
When the export is finished, we can let the master resume his work, by issuing this command in the previous mysql session:
We can proceed to transfer the dump file to the slave; use the method you prefer. On the slave, copy the dump file in the running container, like this:
docker cp thedump.sql.gz my-container:/thedump.sql.gz
Then we import it:
docker exec my-container sh -c 'zcat /thedump.sql.gz | mysql -u root -p"$MYSQL_ROOT_PASSWORD" aWordpressDatabase' docker exec my-container sh -c 'rm /thedump.sql.gz'
Replace my-container, aWordpressDatabase with the correct values.
Start the slave
Open a mysql console in the slave:
docker exec -ti my-container sh -c 'mysql -u root -p"$MYSQL_ROOT_PASSWORD" aWordpressDatabase'
And configure the replication:
STOP SLAVE; RESET SLAVE; CHANGE MASTER TO MASTER_HOST='master.host', MASTER_USER='repluser', MASTER_PASSWORD='aRandomPassword', MASTER_PORT=3306, MASTER_LOG_FILE='binlog.000007', MASTER_LOG_POS=1234567, MASTER_HEARTBEAT_PERIOD=60, MASTER_CONNECT_RETRY=10; START SLAVE;
Check replication status
docker exec -ti my-container sh -c 'mysql -u root -p"$MYSQL_ROOT_PASSWORD" aWordpressDatabase -e "SHOW SLAVE STATUS\G;"'
*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: master.host Master_User: repluser Master_Port: 3306 Connect_Retry: 10 Master_Log_File: binlog.000008 Read_Master_Log_Pos: 12392188 Relay_Log_File: backup-slave-relay-bin.000002 Relay_Log_Pos: 51478388 Relay_Master_Log_File: binlog.000007 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: aWordpressDatabase Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 252550180 Relay_Log_Space: 116716314 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 31939 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_SSL_Crl: Master_SSL_Crlpath: Using_Gtid: No Gtid_IO_Pos: Replicate_Do_Domain_Ids: Replicate_Ignore_Domain_Ids: Parallel_Mode: conservative 1 row in set (0.00 sec)
In a normal condition, Slave_IO_Running and Slave_SQL_Running should be Yes: this means that the communication between master and slave is ok and no sync error has occourred. Seconds_Behind_Master is usually 0, except wen the communication has just been established and the slave has to catch up with the master. This situation usually resolves in some time.
Make the slave read-only
For a safety measure, we're enabling read-only mode for the slave: we don't have to run any query on this host, we're just using it for scheduled backups. Reopen docker-compose.yaml, change READONLY=0 to READONLY=1 and restart the container:
docker-compose up -d
How to use different mariadb version
The default is mariadb 10.5. If you want, you can use mariadb 10.4, 10.3, or 10.2 by changing this line in docker-compose.yaml: