MySQL

Setup

How to install MySQL from distro repositories

MySQL is currently available on almost any distro (if not all), and must be available by installing the package mysql-server. Additionally, mysql-client could be installed to provide access to the local server.

How to install MySQL from Oracle

• Go to www.mysql.com
• Register and Download the communite version
  • Those packages are currently named with capitalized names, usually you might need:
    • MySQL-server
    • MySQL-client
    • MySQL-shared
    • MySQL-shared-compat
    • MySQL-devel
• Uninstall mysql

Centos

# yum remove 'mysql-*'

Ubuntu

# apt-get remove --purge 'mysql-*' 'libmysql*'

• Install mysql from the packages fetched from mysql.com
• Once the installation is done run mysql_secure_installation
• Setup root's initial password

How to change the database location

• Edit /etc/mysql/my.cnf and change the location at datadir parameter
• Remove mysql file from apparmor: /etc/apparmor.d/usr.sbin.mysqld
• Restart apparmor

Root account

How to setup root's initial password

On Oracle MySQL installation, once the installation is done, do:

• Fetch the password from /root/.mysql_secret
• Get into mysql with the above password

 # mysql -p$(cat /root/.mysql_secret)

• Set the new password for root

 > SET PASSWORD = PASSWORD('new_password')

While SET PASSWORD is not being executed, the mysql prompt will ALWAYS return the following message:

ERROR 1820 (HY000): You must SET PASSWORD before executing this statement

How to reset root password

• Stop mysql service
• Create a file /tmp/reset.txt

UPDATE mysql.user SET Password=PASSWORD('new_pass') WHERE User='root';
FLUSH PRIVILEGES;

• Start mysql_safe with the --init-file option

# mysqld_safe --init-file=/tmp/reset.txt

• Remove /tmp/reset.txt

User Accounts

How to create MySQL user with root privileges

 GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION;

How to create MySQL user for a particular database

 CREATE database abc;
 GRANT ALL PRIVILEGES ON abc.* to 'abc'@'localhost' IDENTIFIED BY 'password';

How to create a backup account

GRANT RELOAD ON *.* TO 'backup'@'localhost';
GRANT CREATE, INSERT, DROP ON mysql.ibbackup_binlog_marker TO 'backup'@'localhost';
GRANT CREATE, INSERT, DROP ON mysql.backup_progress TO 'backup'@'localhost';
GRANT CREATE, INSERT, SELECT, DROP ON mysql.backup_history TO 'backup'@'localhost';
GRANT REPLICATION CLIENT ON *.* TO 'backup'@'localhost';
GRANT SUPER ON *.* TO 'backup'@'localhost';
GRANT CREATE TEMPORARY TABLES ON mysql.* TO 'backup'@'localhost';
FLUSH PRIVILEGES;

How to create a MySQL user with backup privileges only

 GRANT SELECT, LOCK TABLES, RELOAD ON *.* TO 'username'@'localhost' IDENTIFIED BY 'password';

Security

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

Replication

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

Data Queries

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 load tabulated data from a file to MySQL

LOAD DATA INFILE file_name REPLACE INTO TABLE table_name FIELDS TERMINATED BY 'data_delimiter'

Troubleshooting

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