Skip to navigation

Command Line Usage Examples

The following examples demonstrate how to use DB Cluster Utils.  I'm using the same database cluster that I set up earlier.

Add a server

If any of the slave servers breakdown and need to be replaced, I need to be able to add servers to the cluster.

For the purposes of these posts, I've set up the DB Cluster Utils configuration file to initialize two slave servers. The server at address 192.168.0.38 hasn't been initialized, so I'm going to add it to the cluster as a slave.

./db_cluster_utils.py -a 192.168.0.38
192.168.0.35 Got master status: mysql-bin.000007, 80022
192.168.0.35 Granted replication privilege to slave_user@192.168.0.38 id'd by mypassword
Initializing new slave
Init server 192.168.0.38
192.168.0.38 Created database my_db
192.168.0.38 Created user db_user
192.168.0.38 Granted privileges
192.168.0.38 Created user slave_user
192.168.0.38 Granted privileges
192.168.0.38 Got master status: mysql-bin.000002, 36483
192.168.0.38 Set read only
CHANGE MASTER TO MASTER_HOST='192.168.0.35', 
                    MASTER_USER='slave_user', 
                    MASTER_PASSWORD='mypassword', 
                    MASTER_LOG_FILE='mysql-bin.000007', 
                    MASTER_LOG_POS=80022;
192.168.0.38 Set master
New slave IP: 192.168.0.38

This command gets the log file and position information from the cluster master, and grants replication privileges to the new slave.  The new slave server is initialized, and the CHANGE MASTER directive is used to tell the slave which node to use as the master server.

At this point, the new slave has joined the cluster, but it doesn't contain any data yet. The database needs to be moved from the master server to the new slave:

./db_cluster_utils.py -m 192.168.0.38
Dump the database on the master server
mysqldump -h 192.168.0.35 -u root -p --opt my_db --result-file=my_db_2014.10.20.133409.sql
Enter password: 
Move the database file
scp my_db_2014.10.20.133409.sql bananapi@192.168.0.38:./my_db_2014.10.20.133409.sql
bananapi@192.168.0.38's password: 
my_db_2014.10.20.133409.sql                                                    100% 3703     3.6KB/s   00:00    
Enter password: 

You will be prompted for the MySQL server's root user password on the master.  Next you'll be prompted for the password of your Linux user account on the master, and finally you need to enter the password of the MySQL server's root user on the new slave.

Start replication across the entire cluster:

./db_cluster_utils.py -s
192.168.0.36 Started slave thread
192.168.0.37 Started slave thread
192.168.0.38 Started slave thread

Make sure replication is working by adding another record to the database on the master server:

mysql> INSERT INTO users VALUES ('James', '123456', 'email@addr.com', CURRENT_DATE());

Query OK, 1 row affected (0.02 sec)
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)

Now if I log into the MySQL shell on the server at 192.168.0.38, and run a query to select all users, I should see the sample data plus the newly added record:

mysql> SELECT * FROM users;
+----------+----------+-----------------+---------------------+
| username | password | email_addr      | joined              |
+----------+----------+-----------------+---------------------+
| Anna     | 123456   | anna@email.com  | 2014-10-20 00:00:00 |
| Bob      | 333456   | bob@addr.com    | 2014-10-20 00:00:00 |
| Claire   | 444456   | claire@addr.com | 2014-10-20 00:00:00 |
| Dave     | 555456   | dave@addr.com   | 2014-10-20 00:00:00 |
| Emma     | 666456   | emma@addr.com   | 2014-10-20 00:00:00 |
| Fred     | 111456   | fred@addr.com   | 2014-10-20 00:00:00 |
| Gwen     | 222456   | gwen@addr.com   | 2014-10-20 00:00:00 |
| Harry    | 123123   | harry@mail.com  | 2014-10-20 00:00:00 |
| Isabelle | 123456   | email@addr.com  | 2014-10-20 00:00:00 |
| James    | 123456   | email@addr.com  | 2014-10-20 00:00:00 |
+----------+----------+-----------------+---------------------+
10 rows in set (0.00 sec)

Switch masters

If you need to do any maintenance on the master server, you may need to take it off line, so there needs to be a way to demote a master and promote a slave to take its place.  The '-d' option can be used to stop replication, revoke replication privileges from the master, and make it read only:

./db_cluster_utils.py -d
192.168.0.36 Stopped slave thread
192.168.0.37 Stopped slave thread
192.168.0.38 Stopped slave thread
192.168.0.36 Reset slave
192.168.0.37 Reset slave
192.168.0.38 Reset slave
192.168.0.35 Revoked replication privilege from slave_user@192.168.0.36
192.168.0.35 Revoked replication privilege from slave_user@192.168.0.37
192.168.0.35 Revoked replication privilege from slave_user@192.168.0.38
192.168.0.35 Set read only

Now the cluster doesn't have a master, so I'm going to promote the slave at IP address 192.168.0.36:

bananapi@master ~/db-cluster-utils $ ./db_cluster_utils.py -p 192.168.0.36
./db_cluster_utils:369: Warning: Slave already has been stopped
  self.curs.execute("STOP SLAVE")
192.168.0.35 Stopped slave thread
192.168.0.36 Stopped slave thread
192.168.0.37 Stopped slave thread
192.168.0.38 Stopped slave thread
192.168.0.36 Cleared read only
Slave IP list:
['192.168.0.35', '192.168.0.37', '192.168.0.38']
192.168.0.36 Granted replication privilege to slave_user@192.168.0.35 id'd by mypassword
192.168.0.36 Granted replication privilege to slave_user@192.168.0.37 id'd by mypassword
192.168.0.36 Granted replication privilege to slave_user@192.168.0.38 id'd by mypassword
192.168.0.36 Got master status: mysql-bin.000031, 32279
192.168.0.35
CHANGE MASTER TO MASTER_HOST='192.168.0.36', 
                    MASTER_USER='slave_user', 
                    MASTER_PASSWORD='mypassword', 
                    MASTER_LOG_FILE='mysql-bin.000031', 
                    MASTER_LOG_POS=32279;
192.168.0.35 Set master
192.168.0.37
CHANGE MASTER TO MASTER_HOST='192.168.0.36', 
                    MASTER_USER='slave_user', 
                    MASTER_PASSWORD='mypassword', 
                    MASTER_LOG_FILE='mysql-bin.000031', 
                    MASTER_LOG_POS=32279;
192.168.0.37 Set master
192.168.0.38
CHANGE MASTER TO MASTER_HOST='192.168.0.36', 
                    MASTER_USER='slave_user', 
                    MASTER_PASSWORD='mypassword', 
                    MASTER_LOG_FILE='mysql-bin.000031', 
                    MASTER_LOG_POS=32279;
192.168.0.38 Set master
192.168.0.35 Started slave thread
192.168.0.37 Started slave thread
192.168.0.38 Started slave thread
Database name: my_db
Master server IP: 192.168.0.36
Control Host: 192.168.0.8
Slave IP address list:
['192.168.0.35', '192.168.0.37', '192.168.0.38']
Root password: T4nk3r!12
User password: mypassword
Slave user password: mypassword
New master IP: 192.168.0.36

This command grants replication privileges on the new master, and tells each slave to use the new master.

In the MySQL shell on the server at 192.168.0.36, I added a record to the database:

mysql> INSERT INTO users VALUES ('Kelly', '123456', 'email@addr.com', CURRENT_DATE());
Query OK, 1 row affected (0.02 sec)

mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)

Now on any of the slaves, if I select all users, I can see the following output:

mysql> SELECT * FROM users;
+----------+----------+-----------------+---------------------+
| username | password | email_addr      | joined              |
+----------+----------+-----------------+---------------------+
| Anna     | 123456   | anna@email.com  | 2014-10-20 00:00:00 |
| Bob      | 333456   | bob@addr.com    | 2014-10-20 00:00:00 |
| Claire   | 444456   | claire@addr.com | 2014-10-20 00:00:00 |
| Dave     | 555456   | dave@addr.com   | 2014-10-20 00:00:00 |
| Emma     | 666456   | emma@addr.com   | 2014-10-20 00:00:00 |
| Fred     | 111456   | fred@addr.com   | 2014-10-20 00:00:00 |
| Gwen     | 222456   | gwen@addr.com   | 2014-10-20 00:00:00 |
| Harry    | 123123   | harry@mail.com  | 2014-10-20 00:00:00 |
| Isabelle | 123456   | email@addr.com  | 2014-10-20 00:00:00 |
| James    | 123456   | email@addr.com  | 2014-10-20 00:00:00 |
| Kelly    | 123456   | email@addr.com  | 2014-10-20 00:00:00 |
+----------+----------+-----------------+---------------------+
11 rows in set (0.00 sec)

Wipe the database

During testing, I often find it useful to remove the databases from each node in the cluster.  The '-w' option allows me to do this:

./db_cluster_utils.py -w
Are you sure you want to wipe database my_db? (y/n): y
192.168.0.36 Stopped slave thread
192.168.0.37 Stopped slave thread
./db_cluster_utils:369: Warning: Slave already has been stopped
  self.curs.execute("STOP SLAVE")
192.168.0.35 Stopped slave thread
192.168.0.35 Dropped user slave_user
192.168.0.35 Dropped user db_user
192.168.0.35 Dropped database my_db
192.168.0.36 Stopped slave thread
192.168.0.36 Dropped user slave_user
192.168.0.36 Dropped user db_user
192.168.0.36 Dropped database my_db
192.168.0.37 Stopped slave thread
192.168.0.37 Dropped user slave_user
192.168.0.37 Dropped user db_user
192.168.0.37 Dropped database my_db
192.168.0.38 Stopped slave thread
192.168.0.38 Dropped user slave_user
192.168.0.38 Dropped user db_user
192.168.0.38 Dropped database my_db

The wipe command effects each server in the server pool, including the master, slaves, and nodes that haven't yet been added to the cluster.  You will be prompted to see if you're sure you want to wipe the databases from the cluster.

Now I can re-initialize the cluster using the '-i' option and start again.

 

Share this page:

comments powered by Disqus