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