database

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

I needed a place to host my scripts, so I opened an account on GitHub. If you are unaware of what GitHub is, its a great place to publish code to the public by using Git; they also offer private hosting for a fee. Even if you do not have a need for this I recommend looking through the site as its a great place to view open source projects.

There are a lot of examples and scripts out on the Internet to automate MySQL backups but not a whole lot written in Python. This was developed to use in a MySQL replication environment and should be run on the slave server. This is an optimal solution as backup’s can run without affecting production. The script dumps each database individually, calculates the size of the sql backup, tarball them up, and emails a log when its done.

To run this the MySQLdb module must be installed on the server. You should also create a user specifically designed to run backups. You can effectively do this by running:

    grant select, lock tables on *.* to [email protected]’localhost’ identified by ‘password’;

The password is stored in base64 format. To encrypt the password for use in the script, run the following command within the Python terminal:

    >>> from base64 import b64encode as encode

    >>> encode(“password”)

    ‘cGFzc3dvcmQ=’

Though this is not the most secure way to encrypt a password to use within a script, it will prevent shoulder surfers.

​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!