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();

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