MySQL All in one ACTIVITY

CentOS Linux 5/6 Install Mysql Database Server
———————————————-
How do I install MySQL database server on CentOS Linux version 5 or version 6 using command line options? How do I configuring MySQL server on CentOS Linux?

You need to install the following packages on CentOS Linux:
Tutorial details
Difficulty Easy (rss)
Root privileges Yes
Requirements yum/Centos/ssh
Estimated completion time 15m

mysql-server : The MySQL server and related files
mysql : MySQL client programs and shared libraries

Additionally, you may need to install the following packages to access mysql using various programming languages:

php-mysql : A module for PHP applications that use MySQL databases.
perl-DBD-MySQL : A MySQL interface for perl.
MySQL-python : An interface to MySQL.

How to install MySQL on CentOS

To install mysql server type the following yum command:
# yum install mysql-server mysql
Sample outputs:
Loaded plugins: downloadonly, fastestmirror, security
Loading mirror speeds from cached hostfile
* base: mirrors.einstein.yu.edu
* extras: mirrors.loosefoot.com
* updates: yum.singlehop.com
Setting up Install Process
Resolving Dependencies
–> Running transaction check
—> Package mysql.x86_64 0:5.1.71-1.el6 will be installed
—> Package mysql-server.x86_64 0:5.1.71-1.el6 will be installed
–> Processing Dependency: perl-DBI for package: mysql-server-5.1.71-1.el6.x86_64
–> Processing Dependency: perl-DBD-MySQL for package: mysql-server-5.1.71-1.el6.x86_64
–> Processing Dependency: perl(DBI) for package: mysql-server-5.1.71-1.el6.x86_64
–> Running transaction check
—> Package perl-DBD-MySQL.x86_64 0:4.013-3.el6 will be installed
—> Package perl-DBI.x86_64 0:1.609-4.el6 will be installed
–> Finished Dependency Resolution

Dependencies Resolved

===================================================================
Package Arch Version Repository
Size
===================================================================
Installing:
mysql x86_64 5.1.71-1.el6 base 893 k
mysql-server x86_64 5.1.71-1.el6 base 8.6 M
Installing for dependencies:
perl-DBD-MySQL x86_64 4.013-3.el6 base 134 k
perl-DBI x86_64 1.609-4.el6 base 705 k

Transaction Summary
===================================================================
Install 4 Package(s)

Total download size: 10 M
Installed size: 29 M
Is this ok [y/N]: y
Downloading Packages:
(1/4): mysql-5.1.71-1.el6.x86_64.rpm | 893 kB 00:00
(2/4): mysql-server-5.1.71-1.el6.x86_64.rpm | 8.6 MB 00:01
(3/4): perl-DBD-MySQL-4.013-3.el6.x86_64.rp | 134 kB 00:00
(4/4): perl-DBI-1.609-4.el6.x86_64.rpm | 705 kB 00:00
——————————————————————-
Total 4.2 MB/s | 10 MB 00:02
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
Installing : perl-DBI-1.609-4.el6.x86_64 1/4
Installing : perl-DBD-MySQL-4.013-3.el6.x86_64 2/4
Installing : mysql-5.1.71-1.el6.x86_64 3/4
Installing : mysql-server-5.1.71-1.el6.x86_64 4/4
Verifying : perl-DBD-MySQL-4.013-3.el6.x86_64 1/4
Verifying : mysql-server-5.1.71-1.el6.x86_64 2/4
Verifying : mysql-5.1.71-1.el6.x86_64 3/4
Verifying : perl-DBI-1.609-4.el6.x86_64 4/4

Installed:
mysql.x86_64 0:5.1.71-1.el6 mysql-server.x86_64 0:5.1.71-1.el6

Dependency Installed:
perl-DBD-MySQL.x86_64 0:4.013-3.el6
perl-DBI.x86_64 0:1.609-4.el6

Complete!

Import CentOS Mysql server files and ports

Mysql server config file: /etc/my.cnf
Mysql default tcp port: 3306 (TCP/UDP)
Mysql server log file: /var/log/mysqld.log
Mysql database storage directory: /var/lib/mysql/
Mysql rc script to start/stop/restart server: /etc/init.d/mysqld {start|stop|restart|reload|status|try-restart}

Howto configure MySQL on CentOS Linux

First, turn on mysql service, type the following chkconfig command:
# chkconfig mysqld on
Howto start / stop / restart mysql server on CentOS Linux

Use any one of the following command to start / stop / restart the mysql server:
# service mysqld start
# service mysqld restart
# service mysqld stop
OR
# /etc/init.d/mysqld start
# /etc/init.d/mysqld stop
# /etc/init.d/mysqld restart
Sample outputs:exit6
Fig.01: Starting / stopping / restarting the mysql server on centos

Fig.01: Starting / stopping / restarting the mysql server on centos

Set the root user password

The root user is mysql admin user. By default there is no password assigned to the root user. Use the following command to set a
new password for the root user:

# mysqladmin -u root password NEWPASSWORD
See how to Change/update the root password for mysqld for more information.
Configure mysqld via /etc/my.cnf config file

Edit /etc/my.cnf, enter:
# vi /etc/my.cnf
Sample default config file:

[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

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

Mysql CentOS Linux firewall configuration

Edit /etc/sysconfig/iptables file, enter:
# vi /etc/sysconfig/iptables
Append the following code open tcp port 3306 on CentOS base server:

-A INPUT -m state –state NEW,ESTABLISHED -m tcp -p tcp –dport 3306 -j ACCEPT

Save and close the file. To restart the firewall on CentOS/RHEL, enter:
# service iptables restart
See CentOS / Redhat Iptables Firewall Configuration Tutorial for more information.
How do I view mysql server logs?

To see server error log use the following tail command:
# tail -f /var/log/mysqld.log
Sample outputs:

131202 11:44:41 [Note] /usr/libexec/mysqld: Shutdown complete
131202 11:44:41 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
131202 11:44:41 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
131202 11:44:41 InnoDB: Initializing buffer pool, size = 8.0M
131202 11:44:41 InnoDB: Completed initialization of buffer pool
131202 11:44:41 InnoDB: Started; log sequence number 0 44233
131202 11:44:41 [Note] Event Scheduler: Loaded 0 events
131202 11:44:41 [Note] /usr/libexec/mysqld: ready for connections.
Version: ‘5.1.71’ socket: ‘/var/lib/mysql/mysql.sock’ port: 3306 Source distribution

How do I connect to mysql server?

Use the mysql command line as follows:
$ mysql -u USER-NAME-HERE -p’PASSWORD-HERE’
$ mysql -u USER-NAME-HERE -p’PASSWORD-HERE’ -h ‘SERVER-NAME-HOST-NAME-HERE’
$ mysql -u USER-NAME-HERE -p’PASSWORD-HERE’ -h ‘SERVER-NAME-HOST-NAME-HERE’ DB-NAME_HERE
$ mysql -u root -p
Sample outputs:

Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.71 Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql>

————————————————————————————————–
How To Start and Stop MySQL Database Server From A Shell Prompt
————————————————————————————————–

Q. How do I start and stop mysql server under Debian / Ubuntu Linux system?

A. Simply use service or /etc/init.d/mysql script to start / stop / restart mysql database server.
Task: Start mysql server

# service mysql start
# /etc/init.d/mysql start

OR
$ sudo service mysql start
$ sudo /etc/init.d/mysql start
Task: Stop mysql server

# service mysql stop
# /etc/init.d/mysql stop

OR
$ sudo service mysql stop
$ sudo /etc/init.d/mysql stop
To restart simply, use:
$ sudo service mysql restart
$ sudo /etc/init.d/mysql restart

————————————————————————————————–
MySQL Change root Password
————————————————————————————————–
How do I change MySQL root password under Linux, FreeBSD, OpenBSD and UNIX-like like operating system over the ssh session?

Setting up MySQL password is one of the essential tasks. By default, root user is MySQL admin account user.
Please note that the Linux or UNIX root account for your operating system and MySQL root user accounts are different.
They are separate, and nothing to do with each other. Sometime you may remove Mysql root account and setup admin user as super user for security purpose.
Method #1: Use mysqladmin command to change root password

If you have never set a root password for MySQL server, the server does not require a password at all for connecting as root.
To setup root password for first time, use mysqladmin command at shell prompt as follows:

$ mysqladmin -u root password NEWPASSWORD

However, if you want to change (or update) a root password, then you need to use the following command:

$ mysqladmin -u root -p’oldpassword’ password newpass

For example, If the old password is abc, you can set the new password to 123456, enter:

$ mysqladmin -u root -p’abc’ password ‘123456’

Note:123456 password is used for demonstration purpose only. You must select a strong password.
It is an important protection to help you have safer MySQL database transactions.
Sample live session from my home server using mysqladmin

Fig.01: mysqladmin command in action

Fig.01: mysqladmin command in action
How do I verify that the new password is working or not?

Use the following mysql command:

mysql -u root -p’123456′ db-name-here

OR

mysql -u root -p’123456′ -e ‘show databases;’
A note about changing MySQL password for other users

To change a normal user password you need to type the following command. In this example, change the password for nixcraft mysql user:
$ mysqladmin -u nixcraft -p’old-password’ password new-password
Method #2: Changing MySQL root user password using the mysql command

This is an another method. MySQL stores username and passwords in user table inside MySQL database.
You can directly update or change the password using the following method for user called nixcraft:
Login to mysql server, type the following command at shell prompt:

$ mysql -u root -p
Use mysql database (type command at mysql> prompt):

mysql> use mysql;

Change password for user nixcraft, enter:

mysql> update user set password=PASSWORD(“NEWPASSWORD”) where User=’nixcraft’;

Finally, reload the privileges:

mysql> flush privileges;
mysql> quit
—————————————————————————————————————————————————-

(OR) Howto Install MySQL on Linux
—————————————————————————————————————————————————-

Most of the Linux distro comes with MySQL. If you want use MySQL, my recommendation is that you download the latest version of MySQL and install it yourself.
Later you can upgrade it to the latest version when it becomes available. In this article,
I will explain how to install the latest free community edition of MySQL on Linux platform.
1. Download the latest stable relase of MySQL

Download mySQL from mysql.com . Please download the community edition of MySQL for your appropriate Linux platform.
I downloaded the “Red Hat Enterprise Linux 5 RPM (x86)”. Make sure to download MySQL Server, Client and “Headers and libraries” from the download page.

MySQL-client-community-5.1.25-0.rhel5.i386.rpm
MySQL-server-community-5.1.25-0.rhel5.i386.rpm
MySQL-devel-community-5.1.25-0.rhel5.i386.rpm

2. Remove the existing default MySQL that came with the Linux distro

Do not perform this on an system where the MySQL database is getting used by some application.

[local-host]# rpm -qa | grep -i mysql
mysql-5.0.22-2.1.0.1
mysqlclient10-3.23.58-4.RHEL4.1

[local-host]# rpm -e mysql –nodeps
warning: /etc/my.cnf saved as /etc/my.cnf.rpmsave
[local-host]# rpm -e mysqlclient10

3. Install the downloaded MySQL package

Install the MySQL Server and Client packages as shown below.

[local-host]# rpm -ivh MySQL-server-community-5.1.25-0.rhel5.i386.rpm MySQL-client-community-5.1.25-0.rhel5.i386.rpm

Preparing… ########################################### [100%]
1:MySQL-client-community ########################################### [ 50%]
2:MySQL-server-community ########################################### [100%]

This will also display the following output and start the MySQL daemon automatically.

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !

To do so, start the server, then issue the following commands:
/usr/bin/mysqladmin -u root password ‘new-password’
/usr/bin/mysqladmin -u root -h medica2 password ‘new-password’

Alternatively you can run:
/usr/bin/mysql_secure_installation

which will also give you the option of removing the test
databases and anonymous user created by default. This is
strongly recommended for production servers.
See the manual for more instructions.
Please report any problems with the /usr/bin/mysqlbug script!
The latest information about MySQL is available at http://www.mysql.com/
Support MySQL by buying support/licenses from http://shop.mysql.com/

Starting MySQL.[ OK ]
Giving mysqld 2 seconds to start

Install the “Header and Libraries” that are part of the MySQL-devel packages.

[local-host]# rpm -ivh MySQL-devel-community-5.1.25-0.rhel5.i386.rpm
Preparing… ########################################### [100%]
1:MySQL-devel-community ########################################### [100%]

Note: When I was compiling PHP with MySQL option from source on the Linux system, it failed with the following error.
Installing the MySQL-devel-community package fixed this problem in installing PHP from source.

configure: error: Cannot find MySQL header files under yes.
Note that the MySQL client library is not bundled anymore!

4. Perform post-install security activities on MySQL.

At a bare minimum you should set a password for the root user as shown below:

[local-user]# /usr/bin/mysqladmin -u root password ‘My2Secure$Password’

The best option is to run the mysql_secure_installation script that will take care of all the typical security related items on the MySQL as shown below.
On a high level this does the following items:

Change the root password
Remove the anonymous user
Disallow root login from remote machines
Remove the default sample test database

[local-host]# /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.
You already have a root password set, so you can safely answer ‘n’.
Change the 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.
Disallow root login remotely? [Y/n] Y
… 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!

5. Verify the MySQL installation:

You can check the MySQL installed version by performing mysql -V as shown below:

[local-host]# mysql -V
mysql Ver 14.14 Distrib 5.1.25-rc, for redhat-linux-gnu (i686) using readline 5.1

Connect to the MySQL database using the root user and make sure the connection is successfull.

[local-host]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 5.1.25-rc-community MySQL Community Server (GPL)

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.

mysql>

Follows the steps below to stop and start MySQL

[local-host]# service mysql status
MySQL running (12588) [ OK ]
[local-host]# service mysql stop
Shutting down MySQL. [ OK ]
[local-host]# service mysql start
Starting MySQL. [ OK ]

———————————————————————————————————————————————
HowTo: Uninstall MySQL Server in Ubuntu Linux
———————————————————————————————————————————————

I’m a new Ubuntu Linux user and my cloud hosting company installed MySQL server by default.
I need to remove it and delete it from my server as I have no use of MySQL server. How can I uninstall MySQL on a Ubuntu based systems?

Typically following Mysql packages are installed on the Debian or Ubuntu Linux systems:

mysql-client – The latest version of MySQL database client.
mysql-server – The latest version of MySQL database server.
mysql-common – MySQL database common files.

How do I uninstall Mysql server?

Just use the apt-get command as follows remove both MySQL server and client in Ubuntu Linux:
sudo apt-get –purge remove mysql-client mysql-server mysql-common
sudo apt-get autoremove

Sample outputs (pay attention to package names):

Reading package lists… Done
Building dependency tree
Reading state information… Done
The following packages were automatically installed and are no longer required:
linux-headers-3.2.0-31-virtual linux-headers-3.2.0-31
Use ‘apt-get autoremove’ to remove them.
The following packages will be REMOVED:
libdbd-mysql-perl* libmysqlclient18* mysql-client* mysql-client-5.5* mysql-common* mysql-server*
mysql-server-5.5*
0 upgraded, 0 newly installed, 7 to remove and 0 not upgraded.
After this operation, 67.5 MB disk space will be freed.
Do you want to continue [Y/n]? y
(Reading database … 105097 files and directories currently installed.)
Removing mysql-server …
Removing mysql-server-5.5 …
mysql stop/waiting
Purging configuration files for mysql-server-5.5 …
Removing mysql-client …
Removing mysql-client-5.5 …
Removing libdbd-mysql-perl …
Removing libmysqlclient18 …
Purging configuration files for libmysqlclient18 …
Removing mysql-common …
Purging configuration files for mysql-common …
dpkg: warning: while removing mysql-common, directory ‘/etc/mysql’ not empty so not removed.
Processing triggers for ureadahead …
Processing triggers for man-db …
Processing triggers for libc-bin …
ldconfig deferred processing now taking place

Delete /etc/mysql/ directory using rm command:
$ sudo rm -rf /etc/mysql/
Understanding apt-get command options

–purge : Remove given packages and config files.
remove : Uninstall packages.
autoremove : Force to remove packages that were automatically installed to satisfy dependencies for other packages and are now no longer needed.

Advertisements
Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: