MYSQL in Linux
MySQL is the more common of the two. It’s relatively easy to install and get working. It’s also very well-supported by third-party tools, as well as by most web hosts. And it’s an extremely fast tool due to the fact that it doesn’t implement the full SQL standard, or as many data types as other solutions (in particular: PostgreSQL). This makes MySQL a great tool to use when writing simple applications that run fast and are easy to set up, but that don’t need to do anything too complex. the default port is 3306.
Installing the database application
# sudo yum install mysql-server
# sudo /sbin/service mysqld start
Manually change the root password and remove an anonymous user later, but to do it the easy way run
this command now
# sudo /usr/bin/mysql_secure_installation
Hit “enter” to give no password for root when that program asks for it. To apply some reasonable security to your new MySQL server say “yes” to all the questions the program asks. In order, those will let you set the root password, remove anonymous users, disable remote root logins, delete the test database the installer included, and then reload the privileges so your changes will take effect.
# sudo yum install mariadb-server mariadb
If you have iptables enabled and want to connect to MySQL from another machine you’ll need to open a port in your server’s firewall (the default port is 3306). You don’t need to do this if the application using MySQL is running on the same machine.
If you do need to open a port (again, only if you’re accessing MySQL from a different machine from the one you’re installing on), you can use the following rules in iptables to open port 3306
-I INPUT -p tcp --dport 3306 -m state --state NEW,ESTABLISHED -j ACCEPT
-I OUTPUT -p tcp --sport 3306 -m state --state ESTABLISHED -j ACCEPT
Starting and stopping the database service:
# sudo /sbin/service mysqld start –to start DB
# sudo /sbin/service mysqld stop – to Stop DB
# sudo systemctl start mariadb.service — start MariaDB
# sudo systemctl stop mariadb.service — stop MariaDB
Launching at reboot
To ensure that the database server launches after a reboot, you must enable the chkconfig utility. Use the following commands to do this.
#sudo chkconfig mysqld on – enable chkconfig on MYSQL
#sudo systemctl enable mariadb.service — Enable chkconfig on MariaDB.
The MYSql shell
There is more than one way to manage a MySQL server, so we’ll focus on the most basic and compatible
approach: The mysql shell.
#/usr/bin/mysql -u root –p -- This will launch the mysql client and enter the shell as user “root”. When you’re prompted for a password enter the one you set at install time or, if you haven’t set one, just hit enter to submit no password.
Setting the root password
If you got in by entering a blank password, or want to change the root password you’ve set, you can do it by entering the following command in the mysql shell. Replace the “password” in quotes with your desired password.
It’s kind of a mouthful. The reason for this is that MySQL keeps user data in its own database, so to change the password we have to run an SQL command to update the database.
Next we’ll reload the stored user information to make our change go into effect.
Some quires you can practice if you are interested
# SELECT User, Host, Password FROM mysql.user; — “SELECT” command tells MySQL you’re asking for data
# CREATE DATABASE demodb; — to create database
# SHOW DATABASES; — to display database
# INSERT INTO mysql.user (User,Host,Password)
VALUES('demouser','localhost',PASSWORD('demopassword')); — Adding database users
# FLUSH PRIVILEGES;
# GRANT ALL PRIVILEGES ON demodb.* to demouser@localhost; — Grant database user permissions.
# SHOW GRANTS FOR 'demouser'@'localhost'; — to check privileges