Administration of a MySQL: Backups

Create a backup of your entire database. Store this backup in a file /var/tmp/mine.sql. This file should contain all data and user accounts you've created so far. We will be restoring this file in question 3.

You must use the command mysqldump with the options --all-databases and --opt.

You can obtain a backup from http://linuxzoo.net/~andrew/mysql/andrew.sql and restore this over your existing data. You can use lwp-request to copy a file from the web server.

lwp-request http://linuxzoo.net/~andrew/mysql/andrew.sql > /var/tmp/andrew.sql

You execute a backup from the linux command prompt.

mysql -u root -psecure < afile.sql

As the backup given includes user account details you should execute the flush privileges command from mysql or use the linux instruction mysqladmin -u root -psecure flush-privileges - having done that you should find that the user scott has disappeared and the user andrew (with password andrew) has been created. Also there is a new database and a new table.

Note that this backup will delete then recreate the user root. In the restored system root has password secure.

On the master

Enable logging on the master

Edit the file /etc/my.cnf so that it includes the following after the [mysql] line.

log-bin
server-id=1

Now restart mysql using the command /etc/init.d/mysql restart

Create a database to replicate

From mysql:

CREATE DATABASE replDB;
USE replDB;
CREATE TABLE one(i INTEGER);
INSERT INTO one VALUES (1);
INSERT INTO one VALUES (2);

Create an account for the slave to use

From within mysql:

GRANT SELECT ON replDB.* TO replUSR@'%' IDENTIFIED BY 'slavepass';
GRANT SELECT ON replDB.* TO replUSR@#'%';

Take a snapshot of the current state of the master

From mysql:

FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS

Make a note of the File and Position values - you will need these later.

Now make a backup of the database replDB - this may be done from unix:

mysqldump --opt --databases test > /tmp/repl.snapshot

You can now release the read lock and add new records to the table one. From mysql:

UNLOCK TABLES
INSERT INTO one VALUES (3);

Allow mysql access through the firewall

Edit /etc/sysconf/iptables so it includes the following before the REJECT line:

-A RH-Firewall-1-INPUT -m state --state NEW -m udp -p udp --dport 3306 -j ACCEPT
-A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT

Restart the firewall:

/etc/init.d/iptables restart

On the slave

Give the slave an id

The slave server must have an id that is different to the master. Edit the my.conf file to include the following after the [mysql] line:

server-id=2

Now restart mysql

/etc/init.d/mysqld restart

Restore a copy of the snapshot.

Get the file repl.backup from the master machine (note that host-1-17 is the hostname of the master, execute hostname on the master to find it).

scp host-1-17.linuxzoo.net:/tmp/repl.snapshot .

Restore the snapshot in the slave:

mysql -u root -psecure < /tmp/repl.snapshot

Give the slave details of the snapshot

From mysql:

CHANGE MASTER TO
        MASTER_HOST='host-1-17',
        MASTER_USER='replUSR',
        MASTER_PASSWORD='slavepass',
        MASTER_LOG_FILE='host-1-17-bin.002',
        MASTER_LOG_POS=184;

We use host-1-17 for the name of the master machine and host-1-17-bin.002 and 184 for the File and Position values you made a note of earlier.

Start the slave threads

Execute the following unix command:

mysqladmin --start-slave

Check it worked

If all went well you should find that the table replDB.one is a copy of the that table on the master.