Difference between revisions of "Mysql"
From Bashlinux
Line 1: | Line 1: | ||
__NOTOC__ |
__NOTOC__ |
||
= MySQL = |
= MySQL = |
||
− | == |
+ | == Howto setup SSL on MySQL server == |
# Create a certificate as described in [[openssl|OpenSSL]] section. |
# 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 |
# Add the following under each directive on `/etc/my.cnf`, if the directive doesn't exists it should be created |
||
Line 44: | Line 44: | ||
# Done |
# Done |
||
− | == Master-Master Replication == |
+ | == How to setup Master-Master Replication on MySQL == |
Ensure the following settings in both servers: |
Ensure the following settings in both servers: |
||
Line 74: | Line 74: | ||
* [[http://www.howtoforge.com/mysql5_master_master_replication_debian_etch]] |
* [[http://www.howtoforge.com/mysql5_master_master_replication_debian_etch]] |
||
* [[http://www.neocodesoftware.com/replication]] |
* [[http://www.neocodesoftware.com/replication]] |
||
+ | |||
− | == InnoDB == |
||
+ | == 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. |
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 |
Revision as of 22:15, 16 December 2011
MySQL
Howto 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
Good links:
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