|
CentOS MySQL 5 Database Replication with SSL Encryption
(contributed by Linus Shio)
Finally, I contribute something on CentOS distro at
1a-centosserver (which brilliantly build by my tai lou and I still not
sure why it named 1a-centosserver). Hahaha……..anyway, it is still
Linux. Ok. MySQL is a very very very nice database server and I really
love it very much.
Basic configuration of MySQL is easy and i lazy to explain here.
Ok. Our main focus is replication. Yeah! Replicate database to other
MySQL server.
First note: This is not a backup solution because any
deleted data will replicate to slave server but protect you again
hardware failure or disaster. |
Here we go:
Step 1. Install MySQL 5 on master server (server A) and slave
server (server B)
# yum install mysql mysql-devel mysql-server
Step 2. Create system startup
# chkconfig –levels 235 mysqld on
# /etc/init.d/mysqld start
Step 3. Create MySQL root password on master server (server A)
# mysqladmin –u root password <password>
# mysqladmin –h server-a.domain –u root password <password>
Step 4. Create MySQL root password on slave server (server B)
# mysqladmin –u root password <password>
# mysqladmin –h server-b.domain –u root password <password>
Step 5. Login to MySQL server on master server (server A) and
slave server (server B) to check if both server
SSL support
# mysql –u root –p
mysql> show variables like ‘%ssl%’;
Step 6. If you see have_openssl, have ssl, ssl_ca, ssl_capath,
ssl_cert, ssl_cipher, ssl_key then the MySQL is SSL support but value
is disabled
Step 7. So we are going to enable SSL (on both
server)
# vi /etc/my.cnf
Add a line with “ssl” under [mysqld] section (Just add, leave other as
it is)
[mysqld]
ssl
Step 8. Restart MySQL service (on both server)
# /etc/init.d/mysqld restart
Step 9. Check again using step 5 and now value
should show enabled (on both server)
Step 10. Now, we focus on master server (server
A) only
# mkdir /var/log/mysql
# chown mysql:mysql /var/log/mysql
Step 11. Create certificates
# mkdir -p /etc/mysql/certs && cd /etc/mysql/certs
# openssl genrsa 2048 > ca-key.pem
# openssl req -new -x509 -nodes -days 3650 -key ca-key.pem
> ca-cert.pem
# openssl req -newkey rsa:2048 -days 3650 -nodes -keyout
server-key.pem > server-req.pem
# openssl x509 -req -in server-req.pem -days 3650 -CA
ca-cert.pem -CAkey ca-key.pem -
set_serial 01 > server-cert.pem
# openssl req -newkey rsa:2048 -days 3650 -nodes -keyout
client-key.pem > client-req.pem
# openssl x509 -req -in client-req.pem -days 3650 -CA
ca-cert.pem -CAkey ca-key.pem -set_serial
01 > client-cert.pem
Step 12. Transfer ca-cert.pem, client-cert.pem
and client-key.pem to slave server (server B) in directory
/etc/mysql/certs (via SCP or any method you prefer)
Step 13. Configure my.cnf in master server (server A)
# vi /etc/my.cnf
Add the line under [mysqld] section (Just add)
ssl-ca=/etc/mysql/certs/ca-cert.pem
ssl-cert=/etc/mysql/certs/server-cert.pem
ssl-key=/etc/mysq/certs/server-key.pem
Step 14. Restart MySQL service
# /etc/init.d/mysqld restart
Step 15. Create user for replication
# mysql –u root –p
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY
'slave_password'
REQUIRE SSL;
mysql> FLUSH PRIVILEGES;
mysql> quit;
Step 16. Configure master server A my.cnf (this example we will
replicate database: testdb)
# vi /etc/my.cnf
Add the following under [mysqld] section
server-id = 1
log-bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M
binlog_do_db = testdb
Restart MySQL service
# /etc/init.d/mysqld restart
Now we want to lock testdb on master server (server A)
# mysql –u root –p
mysql> USE testdb;
mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;
Please write down the output as we will need them later
Step 17. Don’t leave the MySQL shell and open
second command line window to create a database dumb for
master server (server A) and transfer it to slave server (server B)
(via SCP or any method you prefer)
Step 18. Create a database dump in master server
(server A)
# cd /tmp
# mysqldump –u root –p<password> --opt testdb > testdb_dump.sql
Step 19. Transfer the testdb_dump.sql to slave server (server B)
/tmp
Step 20. Close the second command line windows
and back to first one
mysql> UNLOCK TABLES;
mysql> quit;
Step 21. Now we focus on slave server (server B) only
Step 22. Configure slave server my.cnf
# vi /etc/my.cnf
Add the following under [mysqld] section
server-id = 2
master-connect-retry = 60
replicate-do-db = testdb
Step 23. Restart MySQL service
# /etc/init.d/mysqld restart
Step 24. Create empty database: testdb on slave server (server
B)
# mysql –u root –p
mysql> CREATE DATABASE testdb;
mysql> quit;
Step 25. Import the SQL dump testdb_dump.sql
# /usr/bin/mysqladmin –user=root –password=<password> stop-slave
# cd /tmp
# mysql –u root –p<password> testdb < testdb_dump.sql
Step 26. Connect to MySQL shell. We need the
value that we wrote down on step 15 and 16
# mysql –u root –p
mysql> CHANGE MASTER TO MASTER_HOST=’<master_server_ip>’, MASTER_USER='slave_user',
MASTER_PASSWORD='slave_password', MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=3096416, MASTER_SSL=1, MASTER_SSL_CA = '/etc/mysql/certs/ca-cert.pem',
MASTER_SSL_CERT = '/etc/mysql/certs/client-cert.pem', MASTER_SSL_KEY =
'/etc/mysql/certs/client-
key.pem';
mysql> START SLAVE;
Check slave status
mysql> SHOW SLAVE STATUS \G
Step 27. It is important both Slave_IO_Running
and Slave_SQL_Running have value Yes.
Step 28. That’s it. Done
Step 29. Whenever or whatever testdb is update on master server
(server A), all changes will replicate to
slave server (server B)
Prepared by Soon Siang, Shio
One of the reader of 1a-centosserver.com
| Note from
1a-centosserver.com webmaster:
Thank you very much again for your
support to make this comprehensive guide and your kindness to share
the knowledge among us. If you have any guide Please do submit for
us :)
|
|