Offline Version on Ubuntu for MySQL

This is the end of configuration section. 

MySQL is one of the most used database worldwide. To manage MySQL database there are lot of open source GUI tools are available. Some of the popular GUI based tools is MySQL workbench, phpMyAdmin etc.

MySQL Workbench is a GUI based visual tool. Workbench is widely used by developers,  DBAs for data modeling, SQL development, and comprehensive administration tools for server configuration, user administration, backup, and much more. This tool is available for Windows, Linux and Mac OS operating system.

In this tutorial we will show how to install MySQL workbench on Ubuntu 14.04. 

Install MySQL Workbench

· Open Terminal

                    #cd <Prerequsite_ Server\MYSQL-Ubuntu-14.04>/MySQLWB
                         #dpkg    -i     *.deb
It will install mysql-workbench and dependent packages in your system.

As an example:

Application Name

Schema file

IVMS Pro

ivms_30.sql

Click Start Import and enter the database password if prompted. This process may take several minutes depending on the size of your database backup.

Troubleshooting :

·  Change the MySQL Root Password

This method exposes the password to the command-line history, these commands should be run as root.

1. Login through mysql command line tool:

# mysql –uroot –poldpassword

2. Run this command:

mysql>SET PASSWORD FOR root@'localhost' = PASSWORD('newpassword');

or

mysql>SET PASSWORD = PASSWORD('newpassword');

·  Reset the MySQL Root Password  [ Procedure 1 ]

               If you forget your root MySQL password, it can be reset.
               1. Stop the current MySQL server instance.
                              #sudo  service  mysql  stop

                                              Or

                               #sudo /etc/init.d/mysql stop

  2.        Start the mysqld configuration with below command: 

#sudo mysqld --skip-grant-tables &

  3. Login to MySQL as root 

#mysql -u root mysql

                After successful  execution , mysql prompt  will appear                     

mysql>

4.   Replace YOURNEWPASSWORD with your new password

               mysql>UPDATE user SET Password=PASSWORD('YOURNEWPASSWORD') WHERE User='root';

mysql> FLUSH PRIVILEGES;

mysql> exit;

·  Reset the MySQL Root Password  [ Procedure 2 ]

               Step 1: Stop MySQL Service.

#sudo stop mysql

               Step 2: Kill all running mysqld.

#sudo killall -9 mysqld

               Step 3: Starting mysqld in Safe mode.

#sudo mysqld_safe --skip-grant-tables --skip-networking &

               Step 4: Start mysql client

#mysql -u root

               Step 5: After successful login, please execute this command to change any password. 

mysql>FLUSH PRIVILEGES;

               Step 6: You can update mysql root password.

mysql>UPDATE mysql.user SET Password=PASSWORD('newpwd') WHERE User='root';

Step 7: Please execute this command.

mysql>FLUSH PRIVILEGES;

·  Database ErrorHost “domain.com' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'

               Sometimes, user can see above log messages during debugging. To resolve this unblock issue, do the below exercise:

               Linux:
                               # mysql -u root -p -e 'flush hosts'

     Windows:

                               mysql> SET GLOBAL max_connect_errors=10000;

How to uninstall mysql completely in ubuntu [ optional ]

1. Open terminal

2. Execute the following commands sequentially

        #apt-get remove - - purge mysql-server mysql-client mysql-common

             #apt-get autoremove

             #apt-get autoclean

How to move the mysql data directory in ubuntu [ Optional ]

• Open the terminal
• Stop MySQL with the command
  /etc/init.d/mysql stop

• Copy the existing data directory (which is located in /var/lib/mysql) using the command
  cp -R -p /var/lib/mysql /opt/new_datadir

• All you need are the data files. Delete the others with the command
  rm /opt/new_datadir
  (You will get a message about not being able to delete some directories, but do not care about them)

• Edit the MySQL configuration file with the command
  nano /etc/mysql/my.cnf

• Find the entry for datadir, change the path to the new data directory.
• But there is a trick involved here. Ubuntu uses some security software called AppArmor that specifies the areas of your filesystem applications are allowed to access. Unless you modify the AppArmor profile for MySQL, you'll never be able to restart MySQL with the new datadir location.
• In the terminal, enter the command
  nano /etc/apparmor.d/usr.sbin.mysqld

• Copy the lines beginning with /var/lib/mysql
• Comment out the originals with hash marks (#), and paste the lines below the originals.
• Now change /var/lib/mysql in the two new lines with /opt/new_datadir.
• Save and close the file.
• Restart the AppArmor profiles with the command
  /etc/init.d/apparmor restart
• Restart MySQL with the command
  /etc/init.d/mysql restart

MySQL should now start without any errors and your data will be stored in the new location.

Backup Database in MySQL Server

 

Backups are important with any kind of data, and this is especially relevant when talking about databases. MySQL can be backed up in a few different ways that we will discuss one of the popular method where we will use utility program  “automysqlbackup” that is available in the ubuntu repositories.

 

This utility can be scheduled to automatically perform backup at regular intervals

 

NOTE : Install the auto mysql backup package in the database server machine itself.




Below steps will explain the procedure to configure step by step

 

Step 1: Create  the directory “automysqlbackup” under /etc

#mkdir –p /etc/automysqlbackup

 

Step 2: Copy configuration files to the proper destination directory
             #cp Prerequisite Server/automysqlbackup/automysqlbackup.conf \
             /etc/automysqlbackup/myserver.conf
             #cp Prerequisite Server/automysqlbackup/automysqlbackup /usr/local/bin

 

Step 3: Set executable permission to the required file

             #chmod a+x  /usr/local/bin/automysqlbackup

 

Step 4: Edit the configuration file with proper database name, database username , database password, backup directory location, port etc.
             #gedit /etc/automysqlbackup/myserver.conf

 

Edit the below few lines to meet your requirement

 

CONFIG_mysql_dump_username='root' CONFIG_mysql_dump_password='root' CONFIG_backup_dir='/opt/databasebackup'

CONFIG_db_names=('ivms_30')   # might be different in your  installation

CONFIG_mysql_dump_port=3306

 

Step 5: Create  a folder  /opt/database-backup . This is the backup file location . We recommend to set this in NAS.
             #mkdir /opt/database-backup

For NAS, the above command will be like this
              #mkdir –p /mnt/nas1/DB-Backup [ Assuming NAS path : /mnt/nas1/DB-Backup ]

 

 

Step 6: Run the below command once from command line for the first time.
             #/usr/local/bin/automysqlbackup /etc/automysqlbackup/myserver.conf

 

This will create all necessary meaningful folders/subfolders and files

Search this directory “/opt/database-backup” to see the structure of the backup folders/files.

 

Step 7: Now to run this script periodically you have to add this in CRONTAB.

 

(i) Open  Terminal

(ii) Open crontab
     #crontab –e 

[ It will ask what  editor should you use (First time) , Please choose   nano  editor. It’s easiest] 

It will open  a window.

 

 Add  the following entry :
  # m h  dom mon dow  command

30 5 * * * /usr/local/bin/automysqlbackup  /etc/automysqlbackup/myserver.conf

 

This backup command execute  everyday at 5:30 morning

 

If you look into the daily directory, you can see a subdirectory for each database, inside of which is a gzipped sql dump. Example  of such a file is “daily_centraldb_2015-12-01_05h30m_Tuesday.sql.gz “.

 

NOTE : You can choose different schedule in CRON settings. To explore, CRONTAB read the manual by executing command                                #man crontab



Restore Database in MySQL Server


Restoration of database is equally important . Here is the explanation of restoration process with Snapshots.

We are taking example of the gzipped schema from the previous page.

Step 1: GUnzip the zipped sql file

            #gunzip  daily_centraldb_2015-12-01_05h30m_Tuesday.sql.gz


Below sql file will be available in the same directory ( Eg: /root ]

            “ daily_centraldb_2015-12-01_05h30m_Tuesday.sql”









Step 2: Open Mysql Workbench:  


Connect MySQL service . During this operation, root password is required.





Click on Data Import /Restore







Browse the unzipped .sql file from explorer.






In this process you have to create database name . ( For example, the database name is “centraldb”).

The name will be different for various product. ( Ex: for ivms rich version database name: ivms_20)

 

Create a new database by clicking button “new” as marked in the below screen.

After creating new database name, select the database from drop down menu. Then Click on “Start Import”






After successful importing, below screen will appear.




 



Database and tables will be populated.

Check table data .

 

Crosscheck database name, username and password with the package which you want to run.