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)

Comments

Popular posts from this blog

Reduce TIME_WAIT Socket Connections