Difference between revisions of "Mysql"

__NOTOC__

= MySQL =

== How to setup SSL on MySQL server ==

# Create a certificate as described in [[openssl|OpenSSL]] section.

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

<pre><nowiki>

[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

</nowiki></pre>

# Restart the server

<pre><nowiki>

service mysqld restart

</nowiki></pre>

# Test SSL on Mysql. Login into MySQL Server

<pre><nowiki>

mysql -u root -p

</nowiki></pre>

# Check SSL Cipher

<pre><nowiki>

mysql> show status like 'Ssl_cipher';

+--------------+-------------------+

| Variable_name| Value |

+--------------+-------------------+

| Ssl_cipher | DHE-RSA-AES256-SHA|

+--------------+-------------------+

1 row in set (0.01 sec)

</nowiki></pre>

# Done

== How to setup Master-Master Replication on MySQL ==

Ensure the following settings in both servers:

<pre><nowiki>

relay-log = /mnt/mysql-binlogs/mysql-relay-bin

auto-increment-increment=2

binlog-format=MIXED

</nowiki></pre>

Primary server only:

<pre><nowiki>

auto-increment-offset=1

server-id=1

</nowiki></pre>

Secondary server only:

<pre><nowiki>

auto-increment-offset=2

server-id=2

</nowiki></pre>

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

# Login as root

<pre><nowiki>

#mysql -u root -p

</nowiki></pre>

# Skip the duplicated key

<pre><nowiki>

mysql>slave stop;

mysql>set global SQL_SLAVE_SKIP_COUNTER = 1;

mysql>slave start;

mysql>show slave status \G;

</nowiki></pre>

# Ensure slave is running

<pre><nowiki>

mysql> show slave status \G;

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

</nowiki></pre>

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