How to Install MySQL Database Server on CentOS 6.2

In this post, i will show you on how to install MySQL server using yum command. Assume that you have installed minimal installation type CentOS 6.2 and has setup your own local yum repository. You can use internet CentOS yum repository instead if you have direct internet connection to your server.

How to Setup Local Yum Repository from CD-ROM/DVD-ROM image on CentOS 6.2

    [root@centos6 ~]# yum install mysql mysql-server
    Loaded plugins: fastestmirror
    Loading mirror speeds from cached hostfile
    Setting up Install Process
    Resolving Dependencies
    --> Running transaction check
    ---> Package mysql.i686 0:5.1.52-1.el6_0.1 will be installed
    ---> Package mysql-server.i686 0:5.1.52-1.el6_0.1 will be installed
    --> Processing Dependency: perl-DBI for package: mysql-server-5.1.52-1.el6_0.1.i686
    --> Processing Dependency: perl-DBD-MySQL for package: mysql-server-5.1.52-1.el6_0.1.i686
    --> Processing Dependency: perl(DBI) for package: mysql-server-5.1.52-1.el6_0.1.i686
    --> Running transaction check
    ---> Package perl-DBD-MySQL.i686 0:4.013-3.el6 will be installed
    ---> Package perl-DBI.i686 0:1.609-4.el6 will be installed
    --> Finished Dependency Resolution
    
    Dependencies Resolved
    
    =================================================================================================
     Package                Arch         Version                  Repository                    Size
    =================================================================================================
    Installing:
     mysql                  i686         5.1.52-1.el6_0.1         CentOS6.2-Repository         898 k
     mysql-server           i686         5.1.52-1.el6_0.1         CentOS6.2-Repository         8.3 M
    Installing for dependencies:
     perl-DBD-MySQL         i686         4.013-3.el6              CentOS6.2-Repository         134 k
     perl-DBI               i686         1.609-4.el6              CentOS6.2-Repository         705 k
    
    Transaction Summary
    =================================================================================================
    Install       4 Package(s)
    
    Total download size: 10 M
    Installed size: 27 M
    Is this ok [y/N]: y
    Downloading Packages:
    -------------------------------------------------------------------------------------------------
    Total                                                             13 MB/s |  10 MB     00:00
    Running rpm_check_debug
    Running Transaction Test
    Transaction Test Succeeded
    Running Transaction
      Installing : perl-DBI-1.609-4.el6.i686                                                     1/4
      Installing : perl-DBD-MySQL-4.013-3.el6.i686                                               2/4
      Installing : mysql-5.1.52-1.el6_0.1.i686                                                   3/4
      Installing : mysql-server-5.1.52-1.el6_0.1.i686                                            4/4
    
    Installed:
      mysql.i686 0:5.1.52-1.el6_0.1               mysql-server.i686 0:5.1.52-1.el6_0.1
    
    Dependency Installed:
      perl-DBD-MySQL.i686 0:4.013-3.el6                  perl-DBI.i686 0:1.609-4.el6
    
    Complete!
    

How to Fix MySQL Database error : Can’t create database ‘newdbname’ (errno: 28)

Symptoms:
When i run the command to create new database on mysql, it was returned with below error message. How to fix this?

    mysql> CREATE DATABASE newdbname;
    ERROR 1006 (HY000): Can't create database 'newdbname' (errno: 28)
    

Findings and Solutions :
This error means that your mysql server does not have enough free space. You need to check the file system size and please remove unwanted software(s) or installers or files. What we suggest is please try to remove the files in /tmp first before you proceed to remove other files of software’s. Or maybe you can ask the system administrator to increase the partition size.

    [root@server ~]# cd /tmp
    [root@server ~]# rm -rf *
    

Restart mysql server:

    [root@server ~]# service mysqld restart
    

Please re-run the below command:

    mysql> CREATE DATABASE newdbname;
    Query OK, 1 row affected (0.00 sec)
    

How to Show the Tables in a MySQL Database Server

In this guide i will guide you the simple step to show the table on the database server. Assume that the wordpress blog has been installed and running fine with the following database details:

    Database Name : wp1db
    Database User : wp1user
    Database Password : wp1password
    

Go to the terminal or ssh to MySQL Database server. You should login as wp1user.

    [root@server ~]# mysql -u wp1user -p
    Enter password:
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 198
    Server version: 5.0.77 Source distribution
    
    Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
    
    
    mysql> status
    --------------
    mysql  Ver 14.12 Distrib 5.0.77, for redhat-linux-gnu (i686) using readline 5.1
    
    Connection id:          204
    Current database:
    Current user:           wp1user@localhost
    SSL:                    Not in use
    Current pager:          stdout
    Using outfile:          ''
    Using delimiter:        ;
    Server version:         5.0.77 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:                 1 hour 10 min 58 sec
    
    Threads: 3  Questions: 3307  Slow queries: 0  Opens: 57  Flush tables: 1  Open tables: 
    29  Queries per second avg: 0.777
    --------------
    
    
    mysql> use wp1db;
    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A
    
    Database changed
    
    
    mysql> show tables;
    +-----------------------+
    | Tables_in_wp1db       |
    +-----------------------+
    | wp_commentmeta        |
    | wp_comments           |
    | wp_links              |
    | wp_options            |
    | wp_postmeta           |
    | wp_posts              |
    | wp_term_relationships |
    | wp_term_taxonomy      |
    | wp_terms              |
    | wp_usermeta           |
    | wp_users              |
    +-----------------------+
    11 rows in set (0.01 sec)
    
    

How to Grant Privileges in MySQL Database Server

In this tutorial, i will guide you how to grant privileges in MySQL database server. You must connect to the MySQL Server as a root user and perform below task:

1. How to Login MySQL DB as a root:

    [root@server ~]# mysql -u root -p
    Enter password:
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 214
    Server version: 5.0.77 Source distribution
    
    Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
    

2. How to Create MySQL Database : wp1db

    mysql> CREATE DATABASE wp1db;
    

3. How to Create user with password : wp1user /wp1password

    mysql> CREATE USER 'wp1user'@'localhost' IDENTIFIED BY 'wp1password';
    

4. How to Grant all privileges on wp1db to wp1user

    mysql> GRANT ALL PRIVILEGES ON wp1db.* to wp1user@localhost ;
    

5. Verify the grant access

    mysql> SHOW GRANTS FOR 'wp1user'@'localhost';
    +---------------------------------------------------------------------------------------+
    | Grants for wp1user@localhost                                                          |
    +---------------------------------------------------------------------------------------+
    | GRANT USAGE ON *.* TO 'wp1user'@'localhost' IDENTIFIED BY PASSWORD '67ca6cf16fbdbef2' |
    | GRANT ALL PRIVILEGES ON `wp1db`.* TO 'wp1user'@'localhost'                            |
    +---------------------------------------------------------------------------------------+
    2 rows in set (0.00 sec)
    

How to Install MySQL Database in Linux Using Yum

I will show you how to install MySQL database server in Linux CentOS using yum command. It very straight forward and easy as long as your server connected directly to internet

    [root@myserver tmp]# yum install mysql-server
    Loaded plugins: fastestmirror
    Loading mirror speeds from cached hostfile
     * addons: ftp.oss.eznetsols.org
     * base: ftp.oss.eznetsols.org
     * epel: bali.idrepo.or.id
     * extras: ftp.oss.eznetsols.org
     * updates: ftp.oss.eznetsols.org
    Setting up Install Process
    Resolving Dependencies
    --> Running transaction check
    ---> Package mysql-server.x86_64 0:5.0.77-4.el5_5.4 set to be updated
    --> Processing Dependency: mysql = 5.0.77-4.el5_5.4 for package: mysql-server
    --> Processing Dependency: perl-DBD-MySQL for package: mysql-server
    --> Running transaction check
    ---> Package mysql.x86_64 0:5.0.77-4.el5_5.4 set to be updated
    ---> Package perl-DBD-MySQL.x86_64 0:3.0007-2.el5 set to be updated
    --> Finished Dependency Resolution
    
    Dependencies Resolved
    
    ================================================================================
     Package              Arch         Version                  Repository     Size
    ================================================================================
    Installing:
     mysql-server         x86_64       5.0.77-4.el5_5.4         updates       9.8 M
    Installing for dependencies:
     perl-DBD-MySQL       x86_64       3.0007-2.el5             base          148 k
    Updating for dependencies:
     mysql                x86_64       5.0.77-4.el5_5.4         updates       4.8 M
    
    Transaction Summary
    ================================================================================
    Install       2 Package(s)
    Upgrade       1 Package(s)
    
    Total download size: 15 M
    Is this ok [y/N]: y
    Downloading Packages:
    
    (1/3): perl-DBD-MySQL-3.0007-2.el5.x86_64.rpm            | 148 kB     00:17
    (2/3): mysql-5.0.77-4.el5_5.4.x86_64.rpm                 | 4.8 MB     06:40
    (3/3): mysql-server-5.0.77-4.el5_5.4.x86_64.rpm          | 9.8 MB     15:12
    --------------------------------------------------------------------------------
    Total                                            11 kB/s |  15 MB     22:20
    Running rpm_check_debug
    Running Transaction Test
    Finished Transaction Test
    Transaction Test Succeeded
    Running Transaction
      Updating       : mysql                                                    1/4
      Installing     : perl-DBD-MySQL                                           2/4
      Installing     : mysql-server                                             3/4
      Cleanup        : mysql                                                    4/4
    
    Installed:
      mysql-server.x86_64 0:5.0.77-4.el5_5.4
    
    Dependency Installed:
      perl-DBD-MySQL.x86_64 0:3.0007-2.el5
    
    Dependency Updated:
      mysql.x86_64 0:5.0.77-4.el5_5.4
    
    Complete!
    

How to Restore MySQL database using PHPMyAdmin

Previously, i have teach you how to backup the MySQL database. Now, i will brief you the simple steps on how to restore MySQL database using phpmyadmin. This steps only applicable for the database that was installed with phpmyadmin such as LAMP server, Wamp server and XAMPP server. Most of the students or for the development peoples has using this phpmyadmin to administer their database because it’s easy to use and it help us to reduce the common mistakes. Please follow this step to restore the MySQL database.

1. Go to this url:

    http://localhost/phpmyadmin/

In the real webhosting, “localhost “should be appear as:

    http://myexample.com:2082/cpsess57339117932/3rdparty/phpMyAdmin/index.php


2. Select Import Menu and select file to import.

3. In the previous guideline, we have teach you how to backup the “wordpress_db.sql” mysql database. We will use the same database to restore.

4. Make sure the .sql file has been selected and click go to proceed.
5. If database restoration/import has successful, it will return this such of result

    Import has been successfully finished, 35 queries executed.

6. Please note the above steps are to restore the MySQL only. To migrate the whole blog, make sure you has copy the whole content in “C:\wamp\www\ “. If the folder name was “blog”, make sure you have copy the “C:\wamp\www\blog“.

How to Install WAMP Server on Windows XP or Windows

WAMP SERVER(WampServer) is a great package for PHP developers or maybe students or maybe the newbie system administrators or webmasters. Generally, Wamp Server consist of Apache, MySQL and PHP. In this tutorial, i will guide you how to install the fresh wamp server.

1. Go to this url to download

    http://www.wampserver.com/en/download.php


2. Download the latest WampServer released. At the moment, the newest version was WampServer 2.1a [24/12/10]. It includes :
– Apache 2.2.17
– Php 5.3.3
– Mysql 5.1.53 (version 64 bits)
– Mysql 5.5.8 (version 32 bits)
– PhpMyadmin 3.2.0.1
– SQLBuddy 1.3.2

3. Please take note on this warning.

    WARNING : do not try to install WampServer 2 over WAMP5.
    If WAMP5 is installed on your computer, save your data, uninstall it and
    delete the WAMP5 directory before installing WampServer 2.

4. In your windows workstation, double click the WampServer2.1e-x32 or WampServer2.1d-x64.exe
5. During the installation, it will prompt you to unblock the http server from windows firewall or not. Simply click unblock to allow the Apache running on your local pc.
6. To start the wamp server, click “Start All Services”.

7. To test your wamp server, please go to any browser, type url :

    http://localhost/
    

If the installation successful, it should return the “WAMPSERVER Homepage

How to backup MySQL database using PHPMyAdmin

The are a few way to backup the MySQL database. One of the easiest way is using the phpmyadmin. This only applicable for the database that was installed with phpmyadmin such as LAMP server, Wamp server and XAMPP server. Most of the students or for the development peoples has using this phpmyadmin to administer their database because it’s easy to use and it help us to reduce the common mistakes. Please follow this step to backup the MySQL database.

1. Go to this url:

    http://localhost/phpmyadmin/

In the real webhosting, “localhost “should be appear as

    http://myexample.com:2082/cpsess57339117932/3rdparty/phpMyAdmin/index.php


2.Select Export Menu and select which database to backup or migrate.

3. At the bottom of the page, select check box “Save as file”, the rename the file such as “wordpress_db.” Make sure that you select the compression type is “zipped” Click Go to proceed.

4. The backup mysql db will be name as “wordpress_db.sql”. Make a copy of this file. We will use this .sql file to import . Proceed to restore MySQL database using PHPMyAdmin.