Revision as of 07:39, 28 February 2015

MySQL

How to setup SSL on MySQL server

1. Create a certificate as described in OpenSSL section.
2. 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
 

1. Restart the server

 service mysqld restart
 

1. Test SSL on Mysql. Login into MySQL Server

 mysql -u root -p
 

1. 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)
 

1. 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
 

Good links:

• 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

1. Login as root

 #mysql -u root -p
 

1. Skip the duplicated key

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

1. Ensure slave is running

 mysql> show slave status \G;

 Slave_IO_Running: Yes
 Slave_SQL_Running: Yes
 

1. if found `Slave_SQL_Running: No` then repeat the procedure to skip the duplicated key