MySQL

From Bashlinux
Jump to: navigation, search

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

How to upgrade to MySQL 5.6 on Ubuntu 12.04

By default Ubuntu 12.04, codename Precise comes with MySQL 5.5. This wiki entry describe the procedure to upgrade to MySQL v5.6

  • Stop MySQL
# service mysql stop
  • Backup the configuration file and DB Filesystem
# cp /etc/mysql/my.cnf /etc/my.cnf
# rsync -a /var/lib/mysql/ /var/lib/mysql-orig/
  • Remove MySQL 5.5.x
# apt-get -y remove --purge 'mysql-*' 'libmysqlclient*'
  • Install MySQL 5.6.x
# dpkg -i $1
  • Setup initd script
# cp /opt/mysql/server-5.6/support-files/mysql.server /etc/init.d/mysql
# update-rc.d mysql defaults
  • Install Oracle's MySQL dependencies
# apt-get -y install libaio1
  • Create mysql user and set the proper ownership on working directories
# useradd -d /var/lib/mysql -s /bin/false mysql
# chown -R mysql:mysql /opt/mysql/server-5.6 /var/lib/mysql/mysql /var/lib/mysql/p* /var/lib/mysql/ib*
  • Setup logs directory
# mkdir -p /var/log/mysql
# chown mysql /var/log/mysql
  • Update mysql paths
# sed -i -e '/basedir/s/\/usr/\/opt\/mysql\/server-5\.6/' /etc/my.cnf
# sed -i -e '/lc-messages-dir/s/\/usr\/share\/mysql/\/opt\/mysql\/server-5\.6\/share/' /etc/my.cnf
# sed -i -e '/includedir/s/^/#/' /etc/my.cnf
  • The "purge" option on apt-get got rid of /var/lib/mysql, so now we need to restore MySQL data
# rsync -a /var/lib/mysql-orig/ /var/lib/mysql/
  • Update information schema
# /opt/mysql/server-5.6/scripts/mysql_install_db --user=mysql --datadir=/var/lib/mysql
# mv /opt/mysql/server-5.6/my.cnf /opt/mysql/server-5.6/old-my_cnf
  • Add new MySQL bin directory to the PATH
# echo "export PATH=\$PATH:/opt/mysql/server-5.6/bin" >> /etc/bash.bashrc
  • Start the new MySQL server
# service mysql start
  • Now you might need to re-login in order to get MySQL binaries being exported to your PATH

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