Useful MySQL Commands
Install MySQL. The following instruction installs Mysql 5.1.x.
$ sudo yum install -y mysql-server.x86_64
$ sudo service mysqld start
To install Mysql 5.8.
$ sudo yum localinstall https://dev.mysql.com/get/mysql57-community-release-el6-8.noarch.rpm
$ sudo yum install mysql-community-server -y
If you already have Mysql older version and want to install a new version. Do a clean uninstall first,
$ sudo yum list installed | grep mysql
$ sudo yum remove mysql-client mysql-server mysql-common mysql-devel
$ sudo rm -rf /var/lib/mysql/
$ sudo rm -rf /etc/my.cnf
Start the mysqld service
$ sudo service mysqld start
$ sudo chkconfig mysqld on
Find temporary password for root
$ sudo grep 'temporary password' /var/log/mysqld.log
Launch mysql
$ mysql -u root -p
<enter the temporary password>
Set password for root user
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'Pass@123!';
If you like to disable password validation in Mysql 5.7.
mysql> uninstall plugin validate_password;
Set root password below v5.6
mysql> user mysql;
mysql> update user set password= PASSWORD('root') where user = 'root';
mysql> flush privileges;
If you forget the root password, follow the instructions in the link to reset the root password.
Create a user 'spark'@'%' and grant all privileges
$ mysql -u root -p
<enter your root password>
mysql> SELECT user, host FROM mysql.user;
mysql> CREATE USER 'spark'@'%' IDENTIFIED BY 'spark';
mysql> REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'spark'@'%';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'spark'@'%';
mysql> FLUSH PRIVILEGES;
Check currently logged in user
mysql> SELECT USER(),CURRENT_USER();
Create admin user with dba permission
CREATE USER 'admin'@'%' IDENTIFIED BY 'bigdata';
GRANT ALL PRIVILEGES ON *.* to 'admin'@'%' WITH GRANT OPTION;
Drop user
mysql> drop user 'spark'@'%';
Create a database pio in MySQL
mysql> create database pio;
Download JDBC jar. As a general convention keep the jars in /usr/share/java
$ cd /usr/share/java
$ wget http://central.maven.org/maven2/mysql/mysql-connector-java/5.1.40/mysql-connector-java-5.1.40.jar
References:
https://www.if-not-true-then-false.com/2010/install-mysql-on-fedora-centos-red-hat-rhel/
http://www.cloudera.com/documentation/enterprise/latest/topics/cm_ig_mysql.html