backup

The first version of PyMyDB Backup has been released and is based on Python 2.4.3. This is a Python script that will back up your MySQL databases, calculate the size of the backed up files, tarball and compress the contents, then email the results.

To use:

    Download the EPEL RPM which can be found at: http://download.fedora.redhat.com/pub/epel/

Install pymydb-0.5.1-1.noarch.rpm which can be found at: GitHub

This script creates the necessary directories and user account

After installation, there will be two scripts placed in /usr/local/bin which are pymydb.py and setup.py

Run the setup.py script, this will configure the pymydb.py script

Logs into the MySQL server

Creates the backup user, sets a password, and gives him select and lock tables privileges

Configures the email settings

Change the permissions on the pymydb.py script

chown root.pymydb pymydb.py

Add the pymydb.py script into cron and make sure to add a password for the system user

You can download it at my GitHub page

​Setting up MySQL replication is an easy process to do. First you need to edit your my.cnf files on your two servers and add the following:

Server 1

    server-id=1

    auto_increment_offset=1

    auto_increment_increment=3

    log-bin=mysql_log

Server 2

    server-id=2

    auto_increment_offset=1

    auto_increment_increment=3

    log-bin=mysql_log

Make sure that the server id’s in the my.cnf file are unique for each server and the auto_increment_increment is n+1 more than the total amount of servers in your environment. This way your slave servers will update correctly. Once that is complete, restart the MySQL Service

    service mysqld restart

To configure your slave user, log into the master and type the following:

    mysql> create user [email protected]’slavehost.example.com’ identified by ‘somepassword’;

    mysql> grant replication slave on *.* to [email protected]’slavehost.example.com’

    mysql> flush privileges;

The next step is to dump the database from your primary server and import it on the slave server. To dump the database:

    mysqldump -u root -p –lock-tables database > database.sql

Import the database on the slave server:

    mysql -u root -p database < database.sql

We need to get the log file and position information from the master server in order to sync it with the slave. First lock the tables so no changes can be made and then show the status.

    mysql> FLUSH TABLES WITH READ LOCK;

    mysql> show master status;

    +——————+————–+———————-+—————————+

    | File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

    +——————+————–+———————-+—————————+

    | mysql_log.000006 | 156005305 | | |

    +——————+————–+———————-+—————————+

    1 row in set (0.00 sec)

Configure the slave server to attach to the master with the correct credentials. Note the MASTER_LOG_FILE and the MASTER_LOG_POS information need to be identical as the master.

    mysql> CHANGE MASTER TO

    -> MASTER_HOST=’masterhost.example.com’,

    -> MASTER_USER=’slaveuser’,

    -> MASTER_PASSWORD=’somepassword’,

    -> MASTER_LOG_FILE=’mysql-bin.000006′,

    -> MASTER_LOG_POS=156005305;

Next start the replication

    mysql> START SLAVE;

Unlock the tables on the master

    mysql> UNLOCK TABLES;

Check to make sure that it is running properly

    mysql> show slave status\G;

    *************************** 1. row ***************************

    Slave_IO_State: Waiting for master to send event

    Master_Host: masterhost.example.com

    Master_User: slaveuser

    Master_Port: 3306

    Connect_Retry: 60

    Master_Log_File: mysql_log.000006

    Read_Master_Log_Pos: 156005305

    Relay_Log_File: mysqld-relay-bin.000146

    Relay_Log_Pos: 107097880

    Relay_Master_Log_File: mysql_log.000006

    Slave_IO_Running: Yes

    Slave_SQL_Running: Yes

    Replicate_Do_DB:

    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: 155994592

    Relay_Log_Space: 107097880

    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: 0

    1 row in set (0.00 sec)

To set up a master-master replication, repeat the process on the second MySQL server.

That’s it!