MySQL supports using SSL to secure connections, including replication. (It can also be used for regular application connections, e.g. from PHP.)
Using SSL with MySQL can provide three benefits:
Replication is excellent for getting a real-time snapshot of your data far away from the original database—the further the replica, the less likely it can be affected by the same outage. But for many organizations, traffic between datacenters (or between one cloud provider's Regions) crosses the public Internet. If there's anything in your database you wouldn't want on the cover of the New York Times, you should encrypt your replication traffic.
In this procedure, we'll configure SSL-protected replication between a master server in Boston and a business-continuity replica in London.
SSH to boston.example.com and check if MySQL is installed (or compiled) with SSL support.
boston ~ $ mysql -u root Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.5.30 MySQL Community Server (GPL) Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> SHOW VARIABLES LIKE 'have_ssl'; +---------------+----------+ | Variable_name | Value | +---------------+----------+ | have_ssl | DISABLED | +---------------+----------+ 1 row in set (0.00 sec) mysql>
We expect a value of DISABLED. This means it is supported, but not configured, which we fix in Step 4.
A value of YES means it is supported and already configured.
A value of NO means it is not supported, and you'll need to reinstall or recompile MySQL, following these instructions.
have_ssl has value DISABLED or YEShave_ssl has value DISABLED or YESAdd a service account on Boston and give it replication privileges. The London server will use this account to authenticate to Boston.
mysql> CREATE USER 'replicator'@'%' IDENTIFIED BY 'SkewDullFameSeen'; Query OK, 0 rows affected (0.00 sec) mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'replicator'@'%'; Query OK, 0 rows affected (0.00 sec) mysql> GRANT SELECT ON important.stuff TO 'replicator'@'%'; Query OK, 0 rows affected (0.00 sec) mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec) mysql>
This grants SELECT rights to the important.stuff table, which aren't necessary for replication, but are necessary for the tests in Step 3 and Step 8.
replicator can log into MySQL on Boston from London with password SkewDullFameSeenIn this step, we'll capture traffic to show that without SSL, database traffic can be intercepted and read over the network. In a hurry? Skip to the next step.
With these settings, the replication account can connect from London to Boston, and MySQL traffic will not be encrypted.
SSH to london.example.com
Start a tcpdump session on London, capturing MySQL traffic to and from Boston
london ~ $ sudo tcpdump -ns 0 host boston.example.com and port 3306 -w /tmp/repl.pcap & [1] 18796 london ~ $ tcpdump: listening on eth0, link-type EN10MB (Ethernet), capture size 65535 bytes
The output from tcpdump starting up obscured your prompt, press enter a few times, then connect to MySQL on Boston using the replicator account.
london ~ $ london ~ $ london ~ $ mysql -u replicator -p'SkewDullFameSeen' -hboston.example.com
View the content of the important.stuff database. In the lab it's filled with random phrases, but in production it could be credit cards, private diary entries, etc.
Make a note of the details of some row.
mysql> select * from important.stuff;
+-----+---------------------+---------------------+
| id | details | happened |
+-----+---------------------+---------------------+
| 1 | tack seam turn eat | 2012-04-16 17:43:02 |
| 2 | size thin win spin | 2012-04-17 13:30:47 |
| 3 | your lost king beef | 2012-04-18 15:25:15 |
| 4 | soar tell monk sold | 2012-04-20 01:47:14 |
| 5 | sign fall eye roar | 2012-04-21 00:53:43 |
......some content not shown
| 296 | milk mule newt man | 2013-04-15 05:30:20 |
+-----+---------------------+---------------------+
296 rows in set (0.01 sec)
mysql> exit
ByeWe used & to start tcpdump as a background job. Look it up the job number.
london ~ $ jobs [1]+ Running sudo tcpdump host boston.example.com and port 3306 > /tmp/repl.pcap &
It's listed as job 1, bring that job from the background to the foreground with fg
london ~ $ fg 1 sudo tcpdump host boston.example.com and port 3306 > /tmp/repl.pcap
Stop tcpdump by pressing control + c
^C36 packets captured 40 packets received by filter 0 packets dropped by kernel london ~ $
Now search the tcpdump output for the detail text you noted above. If that text is in the dump file, it was transmitted over the network unencrypted.
london ~ $ grep --text "milk mule newt man" /tmp/repl.pcap x crew2013-03-18 02:44:02+277flap hugs many ink2013-03-19 11:38:54,278slug bold spin rail2013-03-21 02:53:51*279deer your mat pig2013-03-22 11:04:34,280cozy they crop drip2013-03-23 18:05:28,281skin span poor debt2013-03-25 09:00:25,282jazz fair pill aqua2013-03-26 21:21:06, 283foam boot they deck2013-03-27 18:11:31,!284wolf memo turf fool2013-03-29 15:59:56,"285knew they thin brow2013-03-30 18:45:58,#286zest dune dent deep2013-03-31 17:09:19,$287real rent cart rake2013-04-02 06:32:01+%288rice prop glow hum2013-04-04 00:37:05*&289bun cart cast did2013-04-05 00:05:20,'290poor twin dome edge2013-04-06 23:23:34,(291gale cash fake were2013-04-07 12:49:16+)292beg dose peel fame2013-04-08 13:47:31,*293they chop unit heap2013-04-10 06:23:35,+294yoke rant torn womb2013-04-12 05:26:06+,295play grow day tuck2013-04-13 22:17:46+-296milk mule newt man2013-04-15 05:30:20. london ~ $
Note that the tcpdump output file is in a binary format, so some characters will display as garbage or mojibake. This file format is designed to be re-used by the tcpdump interpreter (the -r flag) or with the Wireshark GUI.
On the Boston server, create a directory to store the Certificates
mysql> exit Bye boston ~ $ sudo mkdir /etc/ssl/certs/mysql boston ~ $ sudo chown you /etc/ssl/certs/mysql boston ~ $ cd /etc/ssl/certs/mysql
Create an SSL Key Pair for Boston.
boston-private.pem is the private key Boston will use to decrypt traffic. This must remain a secret to Boston alone.
boston-public.pem is the public key Boston will give to clients (like London). Clients will use this key to authenticate Boston's identity, and to encrypt traffic.
boston /etc/ssl/certs/mysql $ openssl req -x509 -newkey rsa:1024 \ -keyout boston-private.pem -out boston-public.pem \ -subj '/CN=boston.example.com' -nodes -days 3650 Generating a 1024 bit RSA private key .............++++++ ...............................++++++ writing new private key to 'boston-private.pem' -----
If two parties do not trust each other directly (e.g., your browser and your bank's web site), they both turn to a mutually trusted Certificate Authority. The CA validates that the server is who they claim to be by signing their certificate.
In MySQL replication, the administrator controls both endpoints. Instead of trusting a CA, London will trust Boston's public key explicitly.
Create a new CA certificate file that contains a copy of Boston's certificate.
boston /etc/ssl/certs/mysql $ cp boston-public.pem ca-cert.pem boston /etc/ssl/certs/mysql $
/etc/ssl/certs/mysql/boston-private.pem/etc/ssl/certs/mysql/boston-public.pem/etc/ssl/certs/mysql/ca-cert.pemConfigure Boston, both to be a replication master (see Establish Replication for details) and to support the new SSL certificates.
ssl-ca - Boston will only accept certificates signed by (or in our case, contained in) this Certificate Authority (CA) certificate.
ssl-cert - This is the public key the Boston server will use to represent itself to London. London will use this to authenticate Boston and to encrypt traffic.
ssl-key - This is the private key Boston will use to decrypt information from London.
Edit my.cnf
boston ~ $ sudoedit /etc/my.cnf
Add these 5 lines
/etc/my.cnf[mysqld] log_bin = mysql-bin server_id = 10 ssl-ca=/etc/ssl/certs/mysql/ca-cert.pem ssl-cert=/etc/ssl/certs/mysql/boston-public.pem ssl-key=/etc/ssl/certs/mysql/boston-private.pem datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock ......some content not shown
The restart the MySQL process
boston ~ $ sudo service mysqld restart Stopping mysqld: [ OK ] Starting mysqld: [ OK ]
/etc/my.cnf sets server_id to 10/etc/my.cnf sets log_bin to mysql-bin/etc/my.cnf sets ssl-ca to /etc/ssl/certs/mysql/boston-public.pem/etc/my.cnf sets ssl-cert to /etc/ssl/certs/mysql/boston-public.pem/etc/my.cnf sets ssl-key to /etc/ssl/certs/mysql/boston-private.pemserver_id has value 10log_bin has value ONssl-ca has value /etc/ssl/certs/mysql/boston-public.pemssl-cert has value /etc/ssl/certs/mysql/boston-public.pemssl-key has value /etc/ssl/certs/mysql/boston-private.pemStill on Boston, edit the replicator account to REQUIRE SSL
boston ~ $ mysql -u root mysql> GRANT USAGE ON *.* TO 'replicator'@'%' REQUIRE SSL; Query OK, 0 rows affected (0.00 sec) mysql> SHOW GRANTS FOR "replicator"; +-------------------------------------------------------------------------------------------------------------------------------------------------------+ | Grants for replicator@% | +-------------------------------------------------------------------------------------------------------------------------------------------------------+ | GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'replicator'@'%' IDENTIFIED BY PASSWORD '*29ED326A0706515E38692D42A8892E51339C8420' REQUIRE SSL | | GRANT SELECT ON `important`.`stuff` TO 'replicator'@'%' | +-------------------------------------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) mysql>
Now, on the London server, try to connect to Boston. Connections that do not request SSL will fail:
london ~ $ mysql -u replicator -p'SkewDullFameSeen' -hboston.example.com ERROR 1045 (28000): Access denied for user 'replicator'@'domU-12-31-39-10-54-BD.compute-1.internal' (using password: YES)
replicator user's GRANT contains REQUIRE SSLreplicator can not log into MySQL on Boston from London with password SkewDullFameSeen without SSLLondon needs a Certificate Authority (CA) to use SSL to connect to Boston.
Create a Place to store the Certificate:
london ~ $ sudo mkdir /etc/ssl/certs/mysql london ~ $ sudo chown you /etc/ssl/certs/mysql london ~ $ cd /etc/ssl/certs/mysql
And copy the CA certificate from Boston:
london /etc/ssl/certs/mysql $ scp boston.example.com:/etc/ssl/certs/mysql/ca-cert.pem . The authenticity of host 'boston.example.com (10.201.219.242)' can't be established. RSA key fingerprint is db:42:29:45:dd:b3:ef:ff:1b:af:e8:10:b7:d5:29:ed. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added 'boston.example.com,10.201.219.242' (RSA) to the list of known hosts. you@boston.example.com's password: (input your password) ca-cert.pem 100% 1718 1.7KB/s 00:00 london /etc/ssl/certs/mysql $
Now connect from London to Boston, using this CA certificate to authenticate Boston's identity:
london ~ $ mysql -u replicator -p'SkewDullFameSeen' -hboston.example.com \
--ssl-ca /etc/ssl/certs/mysql/ca-cert.pem --ssl-verify-serverOnce you connect, check the Ssl_cipher status variable. The connection is encrypted, Ssl_cipher will show what cypher is in use. (Default would be blank.)
mysql> SHOW STATUS LIKE 'Ssl_cipher'; +---------------+--------------------+ | Variable_name | Value | +---------------+--------------------+ | Ssl_cipher | DHE-RSA-AES256-SHA | +---------------+--------------------+
/etc/ssl/certs/mysql/ca-cert.pemreplicator can log into MySQL with SSL from London to Boston with password SkewDullFameSeen Ssl_cipher is not blank.In this step, we'll capture traffic again to show that database traffic is encrypted. In a hurry? Skip to the next step.
First, set up another tcpdump
mysql> exit Bye london ~ $ sudo tcpdump -ns 0 host boston.example.com and port 3306 -w /tmp/repl.pcap & [1] 18796 london ~ $ tcpdump: listening on eth0, link-type EN10MB (Ethernet), capture size 65535 bytes
Press enter a few times, then connect to MySQL on Boston using the replicator account and the new CA certificate.
london ~ $
london ~ $
london ~ $ mysql -u replicator -p'SkewDullFameSeen' -hboston.example.com \
--ssl-ca /etc/ssl/certs/mysql/ca-cert.pem --ssl-verify-serverAgain, view the content in the important.stuff database, and again, make a note of the details of some row.
mysql> select * from important.stuff;
+-----+---------------------+---------------------+
| id | details | happened |
+-----+---------------------+---------------------+
| 1 | tack seam turn eat | 2012-04-16 17:43:02 |
| 2 | size thin win spin | 2012-04-17 13:30:47 |
| 3 | your lost king beef | 2012-04-18 15:25:15 |
| 4 | soar tell monk sold | 2012-04-20 01:47:14 |
| 5 | sign fall eye roar | 2012-04-21 00:53:43 |
......some content not shown
| 296 | milk mule newt man | 2013-04-15 05:30:20 |
+-----+---------------------+---------------------+
296 rows in set (0.01 sec)
mysql> exit
ByeLook up the job number tcpdump started with.
london ~ $ jobs [1]+ Running sudo tcpdump host boston.example.com and port 3306 > /tmp/repl.pcap &
Bring that job to the foreground, and press control + c to end it.
london ~ $ fg 1 sudo tcpdump host boston.example.com and port 3306 > /tmp/repl.pcap ^C36 packets captured 40 packets received by filter 0 packets dropped by kernel london ~ $
This time when you search the tcpdump output for the detail text you noted above, you won't find it. The connection is now encrypted.
london ~ $ grep --text "milk mule newt man" /tmp/repl.pcap london ~ $
On Boston, edit the replicator account's SSL REQUIRE statement to look for a specific certificate Subject.
mysql> GRANT USAGE ON *.* TO 'replicator'@'%' REQUIRE SUBJECT '/CN=london.example.com'; Query OK, 0 rows affected (0.00 sec) mysql> SHOW GRANTS FOR "replicator"; +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Grants for replicator@% | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'replicator'@'%' IDENTIFIED BY PASSWORD '*29ED326A0706515E38692D42A8892E51339C8420' REQUIRE SUBJECT '/CN=london.example.com' | | GRANT SELECT ON `important`.`stuff` TO 'replicator'@'%' | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) mysql>
Now, connections from London to Boston will fail, because London does not have a certificate with that Subject:
london ~ $ mysql -u replicator -p'SkewDullFameSeen' -hboston.example.com \
--ssl-ca /etc/ssl/certs/mysql/ca-cert.pem --ssl-verify-server
ERROR 1045 (28000): Access denied for user 'replicator'@'domU-12-31-39-06-26-67.compute-1.internal' (using password: YES)Note that the connection fails before Boston evaluates whether London provided the correct password.
replicator user's GRANT contains REQUIRE SUBJECT '/CN=london.example.com'replicator can not log into MySQL on Boston from London with password SkewDullFameSeen without a trusted key.Create a Key and Self-Signed Certificate for London. Note that the subject (-subj) is what Boston is already expecting.
london-private.pem is the private key London will use to decrypt traffic, and must remain a secret to London alone.
london-public.pem is the public key Boston will use to authenticate London's identity, and to encrypt traffic.
london /etc/ssl/certs/mysql $ openssl req -x509 -newkey rsa:1024 \ -keyout london-private.pem -out london-public.pem \ -subj '/CN=london.example.com' -nodes -days 3650 Generating a 1024 bit RSA private key .............++++++ ...............................++++++ writing new private key to 'london-private.pem' -----
Append the new London certificate into the CA certificate.
london /etc/ssl/certs/mysql $ cat london-public.pem >> ca-cert.pem
And copy the amended CA certificate back to Boston:
london /etc/ssl/certs/mysql $ scp ca-cert.pem boston.example.com:/etc/ssl/certs/mysql/ you@boston.example.com's password: (input your password) ca-cert.pem 100% 1718 1.7KB/s 00:00 london /etc/ssl/certs/mysql $
Boston needs a MySQL restart to load the changes to ca-cert
boston ~ $ sudo service mysqld restart Stopping mysqld: [ OK ] Starting mysqld: [ OK ]
/etc/ssl/certs/mysql/boston-private.pem/etc/ssl/certs/mysql/boston-public.pem/etc/ssl/certs/mysql/ca-cert.pemThe key files have all been owned by you to this point to make creating and copying them easier.
That's not a good idea long term, so tighten up the file permissions.
The certificates will be owned by the mysql user, (the same headless account the MySQL process runs as). Because in normal operation the certificates don't change, they will be marked read-only—if the certificates need to be replaced or altered later, use sudo to intentionally escalate privileges, with appropriate logging and access controls.
Run this procedure on both servers:
london ~ $ sudo chown -R mysql:mysql /etc/ssl/certs/mysql london ~ $ sudo chmod a=r /etc/ssl/certs/mysql/* london ~ $ ls -l /etc/ssl/certs/mysql total 12 -r--r--r-- 1 mysql mysql 916 Apr 17 19:19 boston-private.pem -r--r--r-- 1 mysql mysql 790 Apr 17 19:19 boston-public.pem -r--r--r-- 1 mysql mysql 1580 Apr 17 19:30 ca-cert.pem london ~ $ sudo chmod a=rx /etc/ssl/certs/mysql london ~ $ ls -ld /etc/ssl/certs/mysql dr-xr-xr-x 2 mysql mysql 4096 Apr 17 19:20 /etc/ssl/certs/mysql
In production, you may choose to make the files only readable by the mysql user (u=r,go=). In the lab, the student account still needs to read the certificates to use the interactive MySQL client.
/etc/ssl/certs/mysql/boston-private.pem is owned by mysql with permissions -r--r--r--/etc/ssl/certs/mysql/boston-public.pem is owned by mysql with permissions -r--r--r--/etc/ssl/certs/mysql/ca-cert.pem is owned by mysql with permissions -r--r--r--/etc/ssl/certs/mysql/london-private.pem is owned by mysql with permissions -r--r--r--/etc/ssl/certs/mysql/london-public.pem is owned by mysql with permissions -r--r--r--/etc/ssl/certs/mysql/ca-cert.pem is owned by mysql with permissions -r--r--r--Connect with MySQL client from London to Boston using new keys.
A client can determine whether the current connection with the server uses SSL by checking the value of the Ssl_cipher status variable. The value of Ssl_cipher is nonempty if SSL is used, and empty otherwise. For example:
london ~ $ mysql -u replicator -p'SkewDullFameSeen' -hboston.example.com \
--ssl-ca /etc/ssl/certs/mysql/ca-cert.pem \
--ssl-cert /etc/ssl/certs/mysql/london-public.pem \
--ssl-key /etc/ssl/certs/mysql/london-private.pem
mysql> SHOW STATUS LIKE 'Ssl_cipher';
+---------------+--------------------+
| Variable_name | Value |
+---------------+--------------------+
| Ssl_cipher | DHE-RSA-AES256-SHA |
+---------------+--------------------+replicator can log into MySQL on Boston from London with password SkewDullFameSeen with a trusted key.On Boston, make a complete backup, including replication master settings, and copy it to London.
boston ~ $ mysqldump -u root --single-transaction --all-databases --master-data=1 > /tmp/master_backup.sql -- Warning: Skipping the data of table mysql.event. Specify the --events option explicitly. boston ~ $ scp /tmp/master_backup.sql london.example.com:/tmp/ The authenticity of host 'london.example.com (10.242.58.189)' can't be established. RSA key fingerprint is 0f:47:42:f4:71:51:4c:a3:70:94:db:83:03:4c:d2:48. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added 'london.example.com,10.242.58.189' (RSA) to the list of known hosts. you@london.example.com's password: (input your password) master_backup.sql 100% 501KB 501.3KB/s 00:00 boston ~ $
/tmp/master_backup.sql/tmp/master_backup.sqlEdit my.cnf
mysql> exit london ~ $ sudoedit /etc/my.cnf
Add these lines under the [mysql] heading. (See Establish Replication for descriptions and the process to verify they've taken effect.)
/etc/my.cnf[mysqld] log_bin = mysql-bin server_id = 20 log_slave_updates = 1 relay_log = mysql-relay-bin read_only = 1 datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock ......some content not shown
Then restart the MySQL process
london ~ $ sudo service mysqld restart Stopping mysqld: [ OK ] Starting mysqld: [ OK ]
/etc/my.cnf sets server_id to 20/etc/my.cnf sets log_bin to mysql-bin/etc/my.cnf sets log_slave_updates to 1/etc/my.cnf sets relay_log to mysql-relay-bin/etc/my.cnf sets read_only to 1server_id has value 20log_bin has value ONlog_slave_updates has value ONrelay_log has value ONread_only has value ONImport the backup from Boston.
You will get dozens of lines of status messages. Scan them briefly to make sure there are no errors.
london ~ $ mysql -u root
mysql> source /tmp/master_backup.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
......some content not shownConfigure London to replicate from Boston.
Most of these settings are the replication versions of the command line arguments you used in Step 12
mysql> CHANGE MASTER TO Master_Host='boston.example.com', Master_User='replicator', Master_Password='SkewDullFameSeen', Master_SSL=1, Master_SSL_CA = '/etc/ssl/certs/mysql/ca-cert.pem', Master_SSL_CERT = '/etc/ssl/certs/mysql/london-public.pem', Master_SSL_KEY = '/etc/ssl/certs/mysql/london-private.pem', Master_SSL_Verify_Server_Cert = 1; Query OK, 0 rows affected (0.04 sec) mysql> slave start; Query OK, 0 rows affected (0.00 sec)
Confirm replication is running.
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: boston.example.com
Master_User: replicator
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 107
Relay_Log_File: mysql-relay-bin.000003
Relay_Log_Pos: 253
Relay_Master_Log_File: mysql-bin.000002
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: 107
Relay_Log_Space: 844
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: Yes
Master_SSL_CA_File: /etc/ssl/certs/mysql/ca-cert.pem
Master_SSL_CA_Path:
Master_SSL_Cert: /etc/ssl/certs/mysql/london-public.pem
Master_SSL_Cipher:
Master_SSL_Key: /etc/ssl/certs/mysql/london-private.pem
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 10
1 row in set (0.00 sec)
mysql>importantimportant.stuffMaster_Host is boston.example.comMaster_User is replicatorSlave_IO_Running is YesSlave_SQL_Running is YesMaster_SSL_Allowed is YesMaster_SSL_CA_File is /etc/ssl/certs/mysql/ca-cert.pemMaster_SSL_Cert is /etc/ssl/certs/mysql/london-public.pemMaster_SSL_Key is /etc/ssl/certs/mysql/london-private.pemSet up a new tcpdump session on London. This time we won't run it in the background:
london ~ $ sudo tcpdump -s 0 -A -vv host boston.example.com and port 3306 tcpdump: listening on eth0, link-type EN10MB (Ethernet), capture size 65535 bytes
Now, insert some new data on the Boston server.
boston ~ $ mysql -u root mysql> INSERT INTO important.stuff SET details='Replication is running encrypted'; Query OK, 1 row affected (0.04 sec) mysql>
The replication packets will appear on London's tcpdump output, almost immediately.
It's not much to look at, because the data is encrypted. The same tcpdump on an unencrypted replication, would show the replicating statement in plain text.
21:01:56.285603 IP (tos 0x0, ttl 61, id 36926, offset 0, flags [DF], proto TCP (6), length 398)
ip-10-110-179-3.ec2.internal.mysql > domU-12-31-39-06-26-67.compute-1.internal.49471: Flags [P.], cksum 0x2e1d (correct), seq 3608204924:3608205270, ack 3612896711, win 142, options [nop,nop,TS val 1997595 ecr 1948794], length 346
E....>@.=..Y
n..
.)....?...|.Xm............
..{....z.... 3......0.6.d.h..F.?...S.b|.+.y......0...9|.o;..2..|+.ty..5...D.><..H&.fxc...=y..S%.cU...C)........,..0.....M._.p Q/x.N.u..`..>...[.f...f]6..[.ef..`.e......8..?d.1.O4.)..s.Q.#....84..ip.l)..o,i....................=....%.M..o.f...HAni...... 2j..o.}..l3@Q"....N.5*..?Z...?c_.....i......L)..I&*...7.'3.{..RL#sV...I..8a........CWo.|...!b..dIr
21:01:56.285659 IP (tos 0x8, ttl 64, id 54699, offset 0, flags [DF], proto TCP (6), length 52)
domU-12-31-39-06-26-67.compute-1.internal.49471 > ip-10-110-179-3.ec2.internal.mysql: Flags [.], cksum 0xf1f8 (incorrect -> 0x72d0), seq 1, ack 346, win 229, options [nop,nop,TS val 1954733 ecr 1997595], length 0
E..4..@.@.s>
.).
n...?...Xm................
......{.
Stop tcpdump by pressing control + c
^C2 packets captured 6 packets received by filter 0 packets dropped by kernel london ~ $
Now log back into MySQL on London and verify the row arrived.
london ~ $ mysql -u root mysql> SELECT * FROM important.stuff ORDER BY id DESC LIMIT 1; +-----+----------------------------------+---------------------+ | id | details | happened | +-----+----------------------------------+---------------------+ | 306 | Replication is running encrypted | 2013-04-17 20:17:17 | +-----+----------------------------------+---------------------+ 1 row in set (0.00 sec) mysql>
important.stuff