MySQL

How to setup SSL on MySQL server

• Create a certificate as described in OpenSSL section.

• Add the following under each directive on /etc/my.cnf, if the directive doesn't exists it should be created

[client]
ssl-ca=/etc/pki/openssl/cacert.pem
ssl-cert=/etc/pki/openssl/client-cert.pem
ssl-key=/etc/pki/openssl/client-key.pem

[mysqld]
ssl-ca=/etc/pki/openssl/cacert.pem
ssl-cert=/etc/pki/openssl/server-cert.pem
ssl-key=/etc/pki/openssl/server-key.pem

• Restart the server

service mysqld restart

• Test SSL on Mysql. Login into MySQL Server

mysql -u root -p

• Check SSL Cipher

mysql> show status like 'Ssl_cipher';
+--------------+-------------------+
| Variable_name| Value             |
+--------------+-------------------+
| Ssl_cipher   | DHE-RSA-AES256-SHA|
+--------------+-------------------+

1 row in set (0.01 sec)

• Done

How to setup Master-Master Replication on MySQL

Ensure the following settings in both servers:

relay-log = /mnt/mysql-binlogs/mysql-relay-bin
auto-increment-increment=2
binlog-format=MIXED

• Primary server only:

auto-increment-offset=1
server-id=1

• Secondary server only:

auto-increment-offset=2
server-id=2

References

http://www.howtoforge.com/mysql5_master_master_replication_debian_etch

How to insert/update records on MySQL with InnoDB engine

When work with this engine don't forget to do a "commit" after any INSERT/UPDATE execution.

How to fix MySQL replication Error_code: 1062

Before to start ensure you already backe up the key in question

• Login as root

# mysql -u root -p

• Skip the duplicated key

mysql>slave stop;
mysql>set global SQL_SLAVE_SKIP_COUNTER = 1;
mysql>slave start;
mysql>show slave status \G;

• Ensure slave is running

mysql> show slave status \G;

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

• if found Slave_SQL_Running: No then repeat the procedure to skip the duplicated key