Tag Archives: MYSQL

MariaDB vs MySQL

Advertisements

Install MySQL on RHEL or Centos

<<<NEXT STEPS>>>______________

check if mysql is already installed earlier. Login as root and run:

root># rpm -ql mysql
package mysql is not installed

root># rpmquery mysql
package mysql is not installed

check linux version:
/root># cat /etc/redhat-release
Red Hat Enterprise Linux Server release 6.9 (Santiago)

root># sudo yum install mysql-server

Installed:

mysql-server.x86_64 0:5.1.73-8.el6_8

Dependency Installed:

mysql.x86_64 0:5.1.73-8.el6_8                          perl-DBD-MySQL.x86_64 0:4.013-3.el6

Complete!

 

<<<NEXT STEPS>>>_________________

Start the database server as necessary (some are automatically started):

root># sudo service mysqld start

<<<NEXT STEPS>>>____________________

Secure your mysql installation. If you make a mistake, simply rerun this:

root># sudo /usr/bin/mysql_secure_installation

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MySQL to secure it, we’ll need the current password for the root user.  If you’ve just installed MySQL, and you haven’t set the root password yet, the password will be blank, so you should just press enter here.

Enter current password for root (enter for none):

OK, successfully used password, moving on…
Setting the root password ensures that nobody can log into the MySQL root user without the proper authorisation.

Set root password? [Y/n] Y

New password: 

Re-enter new password:
Password updated successfully!
Reloading privilege tables..
… Success!

By default, a MySQL installation has an anonymous user, allowing anyone to log into MySQL without having to have a user account created for them.  This is intended only for testing, and to make the installation go a bit smoother.  You should remove them before moving into a production environment.

Remove anonymous users? [Y/n] Y

… Success!

Normally, root should only be allowed to connect from ‘localhost’.  This ensures that someone cannot guess at the root password from the network. Set to ‘N’ if you will login from remote clients.

Disallow root login remotely? [Y/n] N

… Success!

By default, MySQL comes with a database named ‘test’ that anyone can access.  This is also intended only for testing, and should be removed before moving into a production environment.

Remove test database and access to it? [Y/n] Y

– Dropping test database…
… Success!
– Removing privileges on test database…
… Success!

Reloading the privilege tables will ensure that all changes made so far will take effect immediately.

Reload privilege tables now? [Y/n] Y

… Success!

Cleaning up…

All done!  If you’ve completed all of the above steps, your MySQL installation should now be secure.

Thanks for using MySQL!

root># mysql –version

mysql  Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1

<<<NEXT STEPS>>>_______________________

Configure MySQL Server

Configure my.cnf (only as necessary).

In CentOS/RHEL/SLES:

root#> vi /etc/my.cnf

[mysqld]

bind-address=0.0.0.0

default-storage-engine=innodb

sql_mode=STRICT_ALL_TABLES

Restart the database server.

root># sudo service mysqld restart

Enable the server to automatically start on boot:

root># sudo chkconfig mysqld on

<<<NEXT STEPS>>>______________________

=====================================================

How to connect to mysql with user/password:

$ mysql -u root  -p
=====================================================

Check basic status of MySql database. Login as root user and run commands:

 

mysql> status;
--------------
mysql Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1

Connection id: 1488
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.1.73 Source distribution
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: latin1
Conn. characterset: latin1
UNIX socket: /var/lib/mysql/mysql.sock
Uptime: 21 hours 38 min 45 sec

Threads: 2 Questions: 34363 Slow queries: 0 Opens: 3159 Flush tables: 1 Open tables: 64 Queries per second avg: 0.440
--------------

mysql> show full processlist;
+------+------+--------------------------------+------+---------+------+-------+-----------------------+
| Id   | User | Host            | db | Command | Time | State | Info |
+------+------+--------------------------------+------+---------+------+-------+-----------------------+
| 29   | hue | myhost.com:59776 | hue | Sleep | 26| | NULL |
| 1488 | root | localhost       | NULL| Query | 0 |   NULL | show full processlist |
| 5546 | hue | myhost.com:35604 | hue | Sleep | 3 | | NULL |
| 5547 | hue | myhost.com:35606 | hue | Sleep | 2 | | NULL |
+------+------+--------------------------------+------+---------+------+-------+-----------------------+
4 rows in set (0.00 sec)


STOP/START/RESTART MySQL on Linux:

sudo /etc/init.d/mysqld start

sudo /etc/init.d/mysqld stop

sudo /etc/init.d/mysqld restart

Some Linux flavours offer the service command too

sudo service mysqld start

sudo service mysqld stop

sudo service mysqld restart

STOP/START/RESTART MySQL on Windows:

On the Windows taskbar (bottom-right) there will be a MySQL Notifier icon. Right click and MySQL57->stop or start or restart

 

<<<NEXT STEPS>>>______________________

Backup MySQL database using commandline:

Below link has good info on backups:

https://www.godaddy.com/help/backup-mysql-databases-on-your-server-linux-17547

#!/bin/bash
mysqldump -uuserid -ppassword dbname > dbbkup.dbname.`date +%Y%m%d`.sql

 

<<<NEXT STEPS>>>______________________

Check errors in the error log:

/etc/my.cnf will show the location of the error log:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

bind-address=0.0.0.0
default-storage-engine=innodb
sql_mode=STRICT_ALL_TABLES

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid