MySQL

From Bashlinux
Revision as of 02:15, 16 June 2015 by Manpaz (talk | contribs)
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

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