Archives

Database only backups in WHM

 Some clients asked us if you can set up only database backups in WHM. For some, this will sound strange, but there are users who want such a feature. 

You can access WHM backup settings via WHM->Backup->Backup Configuration. Unfortunately, there is no option at this moment to backup only users’ databases.

whm database backup

As of September 25, 2018, this cPanel request is in “Open Discussion” status. You can comment and vote at cPanel Feature Requests Site

 

What solutions do you have? You can use bash commands. One command to backup all the databases on the server to a .gz archive is:

root@www [/backup]# mysqldump --all-databases | gzip > /backup/$(date +%Y-%h-%d)-alldatabases.sql.gz

This will create a backup with all MySQL/MariaDB databases on the server. The filename will include the current date. (e.g. 2018-Sep-25-alldatabases.sql.gz) You can modify the command per your needs. You can also create a cron job to run it at specific times.

Another possibility is to backup each database on the server individually. For this create a new file like backupdbs.sh  The content of the file is:

#!/bin/bash

mysql=/usr/bin/mysql
mysqldump=/usr/bin/mysqldump
date=$(date +%Y-%h-%d)

backupdir="/backup"
mkdir -p $backupdir/$date

databases=`$mysql -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema)"`
for db in $databases; do
echo $db
$mysqldump --opt --databases $db | gzip > "$backupdir/$date/$db.sql.gz"
done

To run the script:

root@www [/backup]# perl backupdbs.sh

As in the previous case, you can also adjust the script per your needs and create a cron job.

Share this post:

How to find when MySQL/MariaDB server was started

We will show you here how you can get the MySQL/MariaDB server started date on a CentOS server. This can be useful for server statistics etc.

1. Use the select now() – interval variable_value second from information_schema.global_status where variable_name=’Uptime’; command at the mysql prompt.

root@web [~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 54690
Server version: 10.1.25-MariaDB MariaDB Server

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]> select now() - interval variable_value second as 'MySQL started on' from information_schema.global_status where variable_name='Uptime';
+----------------------------+
| MySQL started on           |
+----------------------------+
| 2017-08-12 02:14:21.000000 |
+----------------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> quit
Bye
root@web [~]#

2. Use the mysqladmin utility with the command mysqladmin ver|grep Uptime

root@web [~]# mysqladmin ver|grep Uptime
Uptime:                 6 days 9 hours 54 min 5 sec
root@web [~]#

3. Use the operating system service command service mysqld status | grep running

root@web [~]# service mysqld status | grep running
Redirecting to /bin/systemctl status  mysqld.service
   Active: active (running) since Sat 2017-08-12 02:14:22 EDT; 6 days ago
root@web [~]#
Share this post:

Top five software applications to access MySQL/MariaDB servers

To manage a MySQL/MariaDB from web you can use the well-known phpMyAdmin script (available from cPanel, from Softaculous etc). But what happens if you want to use a Windows/Linux/Mac program to manage your databases? We will list here some programs that allow you to work with databases. With these programs you can connect to a local MySQL server or to a remote one. Also, all these programs have GUI – graphical user interface.

Notice that most web hosts block MySQL/MariaDB port 3306. Ask your host to add an exception in the firewall for your IP.

The best option is to download and test each of them to see which one do you like the most. So, in no particular order:

1.SQLyog

SQLyog is the most powerful MySQL administration tool for DBAs, developers and database architects.

sqlyog
SQLyog Window – Click image to enlarge

Homepage: SQLyog
Price: from $99($69 license+ $30 support); free updates for 1 year.
Info:
– strong/well-known product
– no Linux/Mac version; only Windows (32/64 bit) version available.

2.MySQL Workbench

MySQL Workbench is a unified visual tool for database architects, developers, and DBAs. MySQL Workbench provides data modeling, SQL development, and comprehensive administration tools for server configuration, user administration, backup, and much more.

MySQL Workbench
MySQL Workbench Window – Click image to enlarge

Homepage: MySQL Workbench
Price: FREE
Info:
– developed by MySQL/Oracle
– has versions for Windows (32/64 bit), Linux, Mac
– can’t use it with MariaDB anymore

3.HeidiSQL

HeidiSQL is a useful and reliable tool designed for web developers using the popular MySQL server, Microsoft SQL databases and PostgreSQL. It enables you to browse and edit data, create and edit tables, views, procedures, triggers and scheduled events.

HeidiSQL
HeidiSQL Window – Click image to enlarge

Homepage: HeidiSQL
Price: FREE
Info:
– free software; you can download installer/portable exe/source code
– no Linux/Mac version; only Windows (32/64 bit) version available.

4.Navicat for MySQL(Support MariaDB)

Navicat for MySQL is the ideal solution for MySQL/MariaDB administration and development. Connect to MySQL and MariaDB databases simultaneously within a single application. This all-inclusive front-end provides an intuitive and powerful graphical interface for database management, development, and maintenance. It delivers a comprehensive set of tools for those new to MySQL/MariaDB as well as professional developers.

Navicat for MySQL
Navicat for MySQL Window – Click image to enlarge

Homepage: Navicat for MySQL
Price: from $169
Info:
– available for Windows (32/64 bit), Mac, Linux

5.dbForge Studio for MySQL

dbForge Studio for MySQL is a universal GUI tool for MySQL and MariaDB database administration, development, and management. The IDE allows to create and execute queries, develop and debug stored routines, automate database object management, analyze table data via an intuitive interface.

dbForge Studio for MySQL
dbForge Studio for MySQL – Click image to enlarge

Homepage: dbForge Studio for MySQL
Price: FREE and Paid from $119.95 (support and upgrades for 1 year)
Info:
– no Linux/Mac version

Share this post:

Working with cPanel MySQL Databases

cPanel allows you to easily manage your databases and users.

Necessary steps to add a new database:

  1. Log in to cPanel
  2. Look for MySQL® Databases (DATABASES section)
  3. Look for Create New Database section. Enter your desired database name and click the Create Database button. The database will be created, a confirmations message will be displayed: Added the database “database_name”.

To check/repair a database:

  1. Log in to cPanel
  2. Look for MySQL® Databases (DATABASES section)
  3. Go the Modify Databases section
  4. Select from the drop-down list the database you want to check(or repair).
  5. Click the Check(or Repair) Database button. In case of issue, you will see them. In case everything is fine, a success message will be displayed: Success: Check/Repair Complete!

To add a new user to the MySQL/MariaDB server:

  1. Log in to cPanel
  2. Look for MySQL® Databases (DATABASES section)
  3. Go the Modify Databases section
  4. Look for MySQL Users->Add New User section
  5. Enter the Username and Password.
  6. Click the Create User button. The new user will be created, you will see a confirmation message: You have successfully created a MySQL user named “username”.

To add a user to a database:

  1. Log in to cPanel
  2. Look for MySQL® Databases (DATABASES section)
  3. Go the Modify Databases section
  4. Look for MySQL Users->Add User To Database section
  5. Choose the User and the Database from the two drop-down lists.
  6. Click the Add button.
  7. Choose the privileges you want to give to the user.
  8. Click the Make Changes button.
cPanel Databases
cPanel Databases
Notice that you can use this tool for both MySQL and MariaDB servers.

Related KB: How to use the cPanel MySQL Database Wizard

Share this post:

How to use the cPanel MySQL Database Wizard

With the cPanel MySQL Database Wizard you can easily create a database and set up a user for it.

How to use the MySQL Database Wizard:

  1. Log in to your cPanel account
  2. Go to MySQL Database Wizard (DATABASES section)
  3. Step1. Enter the new database name . Click the Next Step button.
  4. Step2. Enter the Username and the Password you want. (you can use the Password Generator) Click the Create User button.
  5. Step3. Now we need to add the user to the database. Select the privileges you want the new user to have on the database. Select ALL PRIVILEGES.  You can also select specific privileges, as:
    ALTER CREATE CREATE TEMPORARY TABLES
    DELETE EVENT INDEX
    ALTER ROUTINE CREATE ROUTINE CREATE VIEW
    LOCK TABLES SELECT TRIGGER
    DROP EXECUTE INSERT
    REFERENCES SHOW VIEW UPDATE

    Click the Next Step button.

  6. Step 4. Nothing to do here. Everything is completed.
cPanel MySQL Database Wizard
cPanel MySQL Database Wizard

 

The how-to movie:

Related KB: Working with cPanel MySQL Databases

Share this post:
Page 1 of 2
1 2