Difference between revisions of "MySQL"

From Bashlinux
Jump to: navigation, search
(Created page with "__NOTOC__ === How to setup SSL on MySQL server === * Create a certificate as described in OpenSSL section. * Add the following under each directive on <tt>/etc/my.cnf</t...")
 
Line 1: Line 1:
  +
== Setup ==
__NOTOC__
 
  +
=== 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 <tt>mysql-server</tt>.
  +
Additionally, <tt>mysql-client</tt> 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:
  +
*** <tt>MySQL-server</tt>
  +
*** <tt>MySQL-client</tt>
  +
*** <tt>MySQL-shared</tt>
  +
*** <tt>MySQL-shared-compat</tt>
  +
*** <tt>MySQL-devel</tt>
  +
* 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 <tt>/etc/mysql/my.cnf</tt> and change the location at <tt>datadir</tt> parameter
  +
* Remove mysql file from apparmor: <tt>/etc/apparmor.d/usr.sbin.mysqld</tt>
  +
* 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 <tt>/root/.mysql_secret</tt>
  +
* 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 <code>SET PASSWORD</code> 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 <tt>/tmp/reset.txt</tt>
  +
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 ===
 
=== How to setup SSL on MySQL server ===
 
* Create a certificate as described in [[OpenSSL]] section.
 
* Create a certificate as described in [[OpenSSL]] section.
Line 31: Line 101:
   
 
* Done
 
* Done
  +
  +
== Replication ==
   
 
=== How to setup Master-Master Replication on MySQL ===
 
=== How to setup Master-Master Replication on MySQL ===
Line 48: Line 120:
 
;References
 
;References
 
:http://www.howtoforge.com/mysql5_master_master_replication_debian_etch
 
:http://www.howtoforge.com/mysql5_master_master_replication_debian_etch
  +
  +
== Data Queries ==
   
 
=== How to insert/update records on MySQL with InnoDB engine ===
 
=== 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 <code>commit</code> after any <code>INSERT/UPDATE</code> 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 ===
 
=== How to fix MySQL replication Error_code: 1062 ===
 
Before to start ensure you already backe up the key in question
 
Before to start ensure you already backe up the key in question

Revision as of 02:15, 16 June 2015

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