Setup MySQL

MySQL the world's second most widely used open-source relational database management system (RDBMS). This post introduce about Setup MySQL and configure it on CentOS 6

1. Install MySQL

   a. Install MySQL by yum

   sudo yum -y install mysql mysql-server mysql-libs mysql-devel
   

   b. Configure MySQL as service

   sudo /sbin/chkconfig mysqld on
   

   c. Start MySQL

   sudo /sbin/service mysqld start
   

   d. Set password for root user by mysqladmin

   sudo /usr/bin/mysqladmin -u root password PASSWORD
   

   e. Set password for root user by mysql_secure_installation

   mysql_secure_installation
   Set root password? [Y/n]
   New password:
   Re-enter new password:
   Remove anonymous users? [Y/n]
   Disallow root login remotely? [Y/n]
   Remove test database and access to it? [Y/n]
   Reload privilege tables now? [Y/n]
   

   f. Connect to MySQL

   sudo mysql -u root -pPASSWORD
   

   g. Change Password of Current User

   mysql> set password = password('PASSWORD');
   

   h. Create Database

   mysql> create database DATABASE character set utf8 collate utf8_unicode_ci;
   mysql> create database DATABASE character set utf8mb4 collate utf8mb4_unicode_ci;
   

   i. Accept Access from an IP

   mysql> create user 'USERNAME'@'192.168.1.5' IDENTIFIED BY 'PASSWORD';
   mysql> grant all on DATABASE.* to 'USERNAME'@'192.168.1.5' identified by 'PASSWORD';
   mysql> grant execute, process, select, show databases, show view, alter, alter routine, create, create routine, create tablespace, create temporary tables, create view, delete, drop, event, index, insert, references, trigger, update on DATABASE.* to 'USERNAME'@'192.168.1.5';
   mysql> alter user 'USERNAME'@'192.168.1.5' with max_queries_per_hour 90;
   

   j. Change Password of User Created

   mysql> set password for 'USERNAME'@'localhost' = password('PASSWORD');
   mysql> set password for 'USERNAME'@'127.0.0.1' = password('PASSWORD');
   mysql> set password for 'USERNAME'@'192.168.1.5' = password('PASSWORD');
   

   k. Import Data

   mysql -u USERNAME -pPASSWORD DATABASENAME < FILE.SQL
   

   l. Export Data

   mysqldump -u USERNAME -pPASSWORD DATABASENAME > FILE.SQL
   

2. Upgrade MySQL

   # -Uvh using in rpm when update package
   rpm -Uvh http://dl.fedoraproject.org/pub/epel/5/x86_64/epel-release-5-4.noarch.rpm
   rpm -Uvh http://rpms.famillecollet.com/enterprise/remi-release-5.rpm
   yum --enablerepo=remi,remi-test list mysql mysql-server
   yum --enablerepo=remi,remi-test install mysql mysql-server
   service mysqld start
   mysql_upgrade -u root -p
   

3. Reset Root Password

   a. Stop MySQL Daemon

   service mysqld stop
   

   b. Start MySQL in Safe Mode

   mysqld_safe --skip-grant-tables &
   

   c. Login

   mysql -uroot
   

   d. Reset Password

   mysql> use mysql;
   mysql> update user set password=PASSWORD("yournewpassword") where User='root';
   mysql> flush privileges;
   mysql> exit;
   

   e. Restart MySQL Deamon

   service mysqld stop
   

4. Connecto to MySQL (EC2) by SSH

   SSH Hostname: 54.250.125.125:22 / ec2-54-250-125-125.us-west-2.compute.amazonaws.com:22

   SSH Username: ec2-user

   SSH Key File: mykey.pem

5. Useful resources

   Cygwin Connect to MySQL

   Start Multiple MySQL Instances

   SSH Tunnel to Remote MySQL (Port Forwarding)