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
-
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
-
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
-
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
-
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
-
Useful resources
Comments
Post a Comment