Login:
mysql -u user -p

List users:
select * from mysql.user;

Select users:
select username from mysql.user;

Delete users:
delete from mysql.user WHERE User=username;

If a user like root is allowed to log onto the MYSQL locally only, the following method works perfect for remote administration.
You will need to know the following information:
1. MYSQL server name
2. MYSQL username
3. MYSQL database name
4. MYSQL listening port.  Usually 3306
5. Install Mysql Navigator on your linux box

Connect to the remote server with a port forward:
ssh -L 6610:127.0.0.1:3306 mysqlusername@servername

Setup new Connection:
Host: 127.0.0.1
User: mysqlusername
Password: userpassword
Database: mysql
Port: 6610
Socket: leave blank
Timeout: 30

Right click on you new connection and click open.

You should now see all of you Database

By: nighthawk

Decompressing a mysql-zrm backup:
mysql-zrm-extract-backup –source-directory /var/lib/mysql-zrm/20120101092426/

__________________________________________________________________________

Start an immediate backup:
mysql-zrm-scheduler –now –backup-set dailyrun –backup-level 0

__________________________________________________________________________

An example crontab setup: (crontab -e)

0 1 * * * /usr/bin/zrm-pre-scheduler –action backup –backup-set dailyrun –backup-level 0 –interval daily
0 4 * * * /usr/bin/mysql-zrm –action purge
0 20 * * * /usr/bin/zrm-pre-scheduler –action backup –backup-set website1 –backup-level 0 –interval daily
15 20 * * * /usr/bin/zrm-pre-scheduler –action backup –backup-set website2 –backup-level 0 –interval daily
45 20 * * * /usr/bin/zrm-pre-scheduler –action backup –backup-set contacts –backup-level 0 –interval daily

Zmanda Recovery Manager [ZRM] for MySQL simplifies the life of a Database Administrator who needs an easy-to-use yet flexible and robust backup and recovery solution for MySQL server.

This article will show you how, in about 15 minutes, you can:

Install ZRM for MySQL locally, on a MySQL Database Server.
Configure ZRM for MySQL locally, on a MySQL Database Server.
Perform a backup.
Setup a automated backup process which adheres to your retention policies.
Backup reports.
Verification of backup images.
Perform a full restoration.

Note:Letters in green are commands that needs to be typed in.

Prerequisites

MySQL Server installed and configured.
At least one running database.
MySQL user with appropriate permissions (backup-user).
Grant access to ‘backup-user’ in mysql with password ‘pass123’
mysql> grant select, insert, update, create, drop, reload, shutdown, alter, super, lock tables, replication client on *.* to ‘backup-user’@’localhost’ identified by ‘pass123’;
mysql> show grants for ‘backup-user’@’localhost’;
Grants for backup-user@localhost
| GRANT SELECT, INSERT, UPDATE, CREATE, DROP, RELOAD, SHUTDOWN, ALTER, SUPER, LOCK TABLES, REPLICATION CLIENT ON *.* TO ‘backup-user’@’localhost’ IDENTIFIED BY PASSWORD ‘591051bb593d2f5b’ WITH GRANT OPTION |

ZRM 1.1 for MySQL requires perl-DBI as well as perl-XML-parser. These perl modules must be installed before ZRM 1.1 for MySQL installation. These perl modules are available in all Linux distributions (might be already installed). Packages are available for these perl modules in the following locations (choose the package based on your distribution) perl-DBI & perl-XML-parser.

TIP: You can copy and paste all of the examples here, making appropriate modifications for your environment.

Install ZRM for MySQL.

1. Log in as root on your Linux Server. Download ZRM for MySQL:

ZRM for MySQL Version: MySQL-zrm-1.1-1 (http://www.zmanda.com/download-zrm.php)

2. Install the ZRM for MySQL 1.1 community edition. Installation of the package should be done as root.

-bash-3.1# ls -lh MySQL-zrm-1*
-rw-rw-r– 1 root root 101K Oct 16 17:29 MySQL-zrm-1.1-1.noarch.rpm

-bash-3.1# rpm -ivh MySQL-zrm-1.1-1.noarch.rpm
Preparing… ########################################### [100%]
1:MySQL-zrm ########################################### [100%]

2b. I also need to run: yum install perl-XML-Parser

3. Verify ZRM for MySQL Installation.

ZRM for MySQL executables are located in /usr/bin:
-bash-3.1# ls -lh /usr/bin/mysql-zrm*
-rwxr-x— 1 root root 86K Oct 16 15:04 /usr/bin/mysql-zrm
-rwxr-x— 1 root root 26K Oct 16 15:04 /usr/bin/mysql-zrm-reporter
-rwxr-x— 1 root root 13K Oct 16 15:04 /usr/bin/mysql-zrm-scheduler

ZRM for MySQL configuration files are located in /etc/mysql-zrm:
-bash-3.1# ls -lh /etc/mysql-zrm/
-rwxr-x— 1 root root 9.1K Oct 16 15:04 mysql-zrm.conf
-rwxr-x— 1 root root 46 Oct 16 15:04 mysql-zrm-release
-rwxr-x— 1 root root 2.8K Oct 16 15:04 mysql-zrm-reporter.conf
drwxr-x— 2 root root 4.0K Oct 16 15:04 plugins
drwxr-x— 4 root root 4.0K Oct 17 11:53 plugin-templates

Additional ZRM for MySQL files are located here:
Man pages /usr/share/man/{man1,man5}
Libraries /usr/lib/mysql-zrm
Log files /var/log/mysql-zrm
Documentation /usr/share/doc/MySQL-zrm-1.1

Configure ZRM for MySQL to backup your database

For this example, we will refer to a database called Movies with a size of 340mb. The Movies Database has two tables: MovieID and MovieRatings.

-bash-3.1# mysql -u backup-user -p
mysql> use movies;
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_movies |
+——————————-+
| MovieID |
| MovieRatings |
+——————————-+
2 rows in set (0.00 sec)

1. Log in as root on your Linux Server.

2. Configure a daily full backup of the Movies Database.

For the purpose of this test, we will create a directory called “dailyrun”, and copy the sample mysql-zrm.conf configuration file into there. This allows more flexibility to create individual backup sets for individual databases.

-bash-3.1# cd /etc/mysql-zrm
-bash-3.1# mkdir dailyrun
-bash-3.1# cp mysql-zrm.conf dailyrun/
-bash-3.1# cd dailyrun/
-bash-3.1# ls -lh
-rwxr-x— 1 root root 8.8k Oct 17 15:58 mysql-zrm.conf

3. Edit /etc/mysql-zrm/dailyrun/mysql-zrm.conf to change the following parameters. Please be sure to protect this file with the proper permissions, as it stores the password for the MySQL ‘backup-user’ in clear text.

For the purpose of this test we are demonstrating a full backup:
Backup Level:
# Backup level. It can be full or incremental
# Use 0 for full and 1 for incremental backups
# This parameter is optional and default value is full backup.
backup-level=0

We will be performing logical backups:
Backup Method:
# Backup method
# Values can be “raw” or “logical”. Logical backup are backups using
# mysqldump(1) tool
# This parameter is optional and default value is “raw”.
backup-mode=logical

For the purpose of this test we chose to retain our backups for 10 days:
Retention Policy:
# Specifies how long the backup should be retained. The value can be
# specified in days (suffix D), weeks (suffix: W), months (suffix: M) or
# years (suffix Y). 30 days in a month and 365 days in a year are assumed
# This parameter is optional and the default is the backups are retained
# forever.
retention-policy=10D

We have chosen to compress our backups to save disk space:

# This parameter should be set to 1 if backups should be compressed. If this
# parameter is set, gzip(1) command is used by default. If different
# compression algorithm should be used, it must be set in “compress-plugin”
# parameter. Default: There is no data compression.
compress=1

We are choosing to backup only the ‘Movies’ database:
Database(s) to backup:
# List of databases that are part of this backup set. Multiple database
# names are separated by space character. This parameter is ignored if
# “all-databases” is set 1.
databases=movies

MySQL username/password that was created above:
(the password below is stored in clear text)
MySQL Server Parameters:
# MySQL database user used for backup and recovery of the backup set.
# This parameter is optional. If this parameter is not specified, values from
# my.cnf configuration file.
user=”backup-user”

# MySQL database user password.
# This parameter is optional. If this parameter is not specified, values from
# my.cnf configuration file or no password is used.
password=”pass123″

This can be turned off, if you like, once MySQL is configured and running:
Verbosity of ZRM for MySQL Logging:
# This parameter controls the verbosity of MySQL ZRM logging. The MySQL ZRM logs
# are available at /var/log/mysql-zrm/mysql-zrm.log. This parameter is optional
# default value is 0 (less verbose).
# The valid values are 0 and 1
verbose=1

Emailing Backup Report:
(requires your Linux server to be able to send mail and a valid email address)
# After a backup run the backup report is emailed to the mailto address
# This parameter is optional and default behavior is not to send mail notifications.
mailto=”[email protected]”

Save and close the /etc/mysql-zrm/dailyrun/mysql-zrm.conf file.

Perform a Backup
1. On your MySQL Server, as root run ZRM for MySQL to start the dailyrun Backup on the Movies Database.

-bash-3.1# mysql-zrm-scheduler –now –backup-set dailyrun

2. ZRM for MySQL will output the following report to screen.

Logging to /var/log/mysql-zrm/mysql-zrm-scheduler.log
INFO: ZRM for MySQL Community Edition – version 1.1
INFO: Input Parameters Used {
INFO: quiet=0
INFO: verbose=1
INFO: retention-policy=1W
INFO: backup-level=0
INFO: destination=/var/lib/mysql-zrm
INFO: databases=movies
INFO: html-reports=backup-status-info
INFO: backup-mode=logical
INFO: password=******
INFO: compress=
INFO: user=backup-user
INFO: Getting mysql variables
INFO: mysqladmin –user=backup-user –password=***** variables
INFO: datadir is /var/lib/mysql/
INFO: mysql_version is 5.0.22-log
INFO: log_bin=ON
INFO: backup set being used is dailyrun
INFO: backup-set=dailyrun
INFO: backup-date=20061025161624
INFO: host=localhost
INFO: backup-date-epoch=1161818184
INFO: retention-policy=1W
INFO: mysql-zrm-version=ZRM for MySQL Community Edition – version 1.1
INFO: mysql-version=5.0.22-log
INFO: backup-directory=/var/lib/mysql-zrm/dailyrun/20061025161624
INFO: Executing pre-backup-plugin
INFO: Flushing the logs
INFO: flush-logs
INFO: Getting master logname using command mysql –user=backup-user –password=***** -e “show master status”
INFO: backup-level=0
INFO: Command used for logical backup is mysqldump –opt –extended-insert –single-transaction –default-character-set=utf8 –create-options –user=backup-user –password=***** –databases movies > “/var/lib/mysql-zrm/dailyrun/20061025161624/backup.sql”
INFO: Logical backup done for the following database(s)
movies
INFO: logical-databases=movies
INFO: next-binlog=mysql-bin.000030
INFO: last-backup=/var/lib/mysql-zrm/dailyrun/20061025160303
INFO: /var/lib/mysql-zrm/dailyrun/20061025161624/backup.sql=497892557fd97f4f8f4102ab866293a6
INFO: backup-size=306.20 MB
INFO: Compressing backup
INFO: Command used is ‘tar –same-owner -cpsC “/var/lib/mysql-zrm/dailyrun/20061025161624” –exclude=backup-data –exclude=index . 2>/tmp/ZQr1wiV0Oi | gzip 2>/tmp/LqilmZDG20 > “/var/lib/mysql-zrm/dailyrun/20061025161624/backup-data” 2>/tmp/NDX91Ym36X’
INFO: compress=
INFO: backup-size-compressed=69.72 MB
INFO: Removing all of the uncompressed/unencrypted data
INFO: Executing post-backup-plugin
INFO: read-locks-time=00:00:32
INFO: flush-logs-time=00:00:00
INFO: backup-time=00:01:08
INFO: backup-status=Backup succeeded
INFO: Backup succeeded

Note:
In this example, we are using the ZRM for MySQL scheduler to do backup now. The schedule can be used to set up a backup schedule, as shown below.
Schedules a daily full at 1am everyday.
-bash-3.1# mysql-zrm-scheduler –add –interval daily –start 01:00 –backup-level 0 –backup-set dailyrun
Logging to /var/log/mysql-zrm/mysql-zrm-scheduler.log DONE
Verification that scheduler has been configured
-bash-3.1# mysql-zrm-scheduler –query
Logging to /var/log/mysql-zrm/mysql-zrm-scheduler.log 0 1 * * * /usr/bin/zrm-pre-scheduler –action backup –backup-set dailyrun –destination /var/lib/mysql-zrm –backup-level 0 –interval daily

Backup Reports
You can run the following mysql-zrm-reporter commands for a quick summary of your backup job(s).

-bash-3.1# mysql-zrm-reporter –where backup-set=dailyrun –show backup-status-info

backup_set backup_date backup_level backup_status comment
——————————————————————————————————————————————-
dailyrun Wed 25 Oct 2006 04:16:24 PM PDT 0 Backup succeeded —-

The first report shows us the status of backup(s) for backup-set ‘dailyrun’, including backup level (full [0] or incremental [1]) & backup status (success or failure).

-bash-3.1# mysql-zrm-reporter –where backup-set=dailyrun –show backup-performance-info

backup_set backup_date backup_level backup_size backup_size_compressed backup_time
—————————————————————————————————————————————————————
dailyrun Wed 25 Oct 2006 04:16:24 PM PDT 0 306.20 MB 69.72 MB 00:01:09

The second report shows the total time the backup(s) ran, as well as the compressed & uncompressed size of the backup(s).

Verification of Backup Images
You can run the following mysql-zrm command to quickly verify the integrity of your last backup.

-bash-3.1# mysql-zrm –action verify-backup –backup-set dailyrun

ZRM for MySQL Community Edition – version 1.1
INFO: Input Parameters Used {
INFO: verbose=1
INFO: retention-policy=1W
INFO: backup-level=0
INFO: databases=movies
INFO: html-reports=backup-status-info
INFO: backup-mode=logical
INFO: password=******
INFO: compress=
INFO: user=backup-user
INFO: Uncompressing backup
INFO: Command used is ‘cat “/var/lib/mysql-zrm/dailyrun/20061025161624/backup-data” | gzip -d | tar –same-owner -xpsC “/var/lib/mysql-zrm/dailyrun/20061025161624” 2>/tmp/bY1jIvcElN’
INFO: checksum for file /var/lib/mysql-zrm/dailyrun/20061025161624/backup.sql is correct
INFO: Verification successful
INFO: Removing all of the uncompressed/unencrypted data

Perform a full restoration
First we’ll drop the movies database. In order to this, we will have to login MySQL with a user that has root privileges. In this example, we have renamed the ‘root’ user to ‘admin’ (for security reasons), within MySQL.

-bash-3.1# mysql -u admin -p
mysql> show databases;
+———————————-+
| Database |
+———————————-+
| information_schema |
| movies |
| mysql |
| test |
+———————————–+
4 rows in set (0.00 sec)

mysql> drop database movies;
Query OK, 2 rows affected (0.67 sec)

mysql> show databases;
+———————————-+
| Database |
+———————————-+
| information_schema |
| mysql |
| test |
+———————————–+
3 rows in set (0.00 sec)

1. Determine which backup to restore from.

-bash-3.1# mysql-zrm-reporter –show restore-info –where backup-set=dailyrun

backup_set backup_date backup_level backup_directory
————————————————————————————————————————————————————————-
dailyrun Wed 25 Oct 2006 04:16:24 PM PDT 0 /var/lib/mysql-zrm/dailyrun/20061025161624

2. Kick off restore job. We’ll restore from the last full backup, from 4:16pm on October 25th.

-bash-3.1# mysql-zrm -restore –backup-set dailyrun –source-directory
/var/lib/mysql-zrm/dailyrun/20061025161624/
INFO: ZRM for MySQL Community Edition – version 1.1
INFO: Input Parameters Used {
INFO: verbose=1
INFO: retention-policy=1W
INFO: backup-level=0
INFO: databases=movies
INFO: source-directory=/var/lib/mysql-zrm/dailyrun/20061025161624
INFO: html-reports=backup-status-info
INFO: backup-mode=logical
INFO: password=******
INFO: compress=
INFO: user=backup-user
INFO: Getting mysql variables
INFO: mysqladmin –user=backup-user –password=***** variables
INFO: datadir is /var/lib/mysql/
INFO: mysql_version is 5.0.22-log
INFO: log_bin=ON
INFO: Uncompressing backup
INFO: Command used is ‘cat “/var/lib/mysql-zrm/dailyrun/20061025161624/backup-data” | gzip -d | tar –same-owner -xpsC “/var/lib/mysql-zrm/dailyrun/20061025161624” 2>/tmp/1wNYPfSl01’
INFO: restoring using command mysql –user=backup-user –password=***** -e “set character_set_client=utf8;set character_set_connection=utf8;set character_set_database=utf8;set character_set_results=utf8;set character_set_server=utf8;source /var/lib/mysql-zrm/dailyrun/20061025161624/backup.sql;”
INFO: Restored database(s) from logical backup: movies
INFO: Shutting down MySQL
INFO: Removing all of the uncompressed/unencrypted data
INFO: Restore done in 140 seconds.
MySQL server has been shutdown. Please restart after verification.

After starting the MySQL service (as root type: /sbin/service mysqld start) Verify the database was restored.

-bash-3.1# mysql -u backup-user -p
mysql> show databases;
+———————————-+
| Database |
+———————————-+
| information_schema |
| movies |
| mysql |
| test |
+———————————–+
4 rows in set (0.00 sec)
mysql> use movies;
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_movies |
+——————————-+
| MovieID |
| MovieRatings |
+——————————-+
2 rows in set (0.00 sec)

Success!
In just about 15 minutes, we installed and configured a fast and reliable MySQL backup solution, performed a backup, verified our backup and performed a restoration. We did it with freely downloadable open source software that you can install from binaries or compile for your unique needs. As a DBA, you now have a reliable and easy to implement backup solution to protect your database using ZRM for MySQL.

By: Zmanda

For the purpose of this article, we are going to use the ‘SELECT’ privilege. All code provided are examples. You will want to make sure that you change:

database to the database name you are using.
username to your database user.
password to a strong password unique to that user. Please read our article: Strong Password Guidelines.

Start by logging into your server via SSH and logging into MySQL entering the following:

mysql -u admin -p`cat /etc/psa/.psa.shadow`

The prompt should now look like this:

mysql>

Enter the following if the database user already exists.:
GRANT SELECT ON database.* TO user@’localhost’;

If you intend to create a brand new user, then run this:
GRANT SELECT ON database.* TO user@’localhost’ IDENTIFIED BY ‘password’;

To enable more options, you would separate them with a comma. So to enable SELECT, INSERT, and DELETE your syntax would look like this:
GRANT SELECT, INSERT, DELETE ON database TO username@’localhost’ IDENTIFIED BY ‘password’;

Once you have given the desired privileges for your user, you will need to run this command within the MySQL command prompt:
FLUSH PRIVILEGES;

To see a list of the privileges that have been granted to a specific user:
select * from mysql.user where User=’username’;

This is a list of privileges that you can grant:
Privilege Meaning
ALL [PRIVILEGES] Sets all simple privileges except GRANT OPTION
ALTER Enables use of ALTER TABLE
CREATE Enables use of CREATE TABLE
CREATE TEMPORARY TABLES Enables use of CREATE TEMPORARY TABLE
DELETE Enables use of DELETE
DROP Enables use of DROP TABLE
EXECUTE Not implemented
FILE Enables use of SELECT … INTO OUTFILE and LOAD DATA INFILE
INDEX Enables use of CREATE INDEX and DROP INDEX
INSERT Enables use of INSERT
LOCK TABLES Enables use of LOCK TABLES on tables for which you have the SELECT privilege
PROCESS Enables the user to see all processes with SHOW PROCESSLIST
REFERENCES Not implemented
RELOAD Enables use of FLUSH
REPLICATION CLIENT Enables the user to ask where slave or master servers are
REPLICATION SLAVE Needed for replication slaves (to read binary log events from the master)
SELECT Enables use of SELECT
SHOW DATABASES SHOW DATABASES shows all databases
SHUTDOWN Enables use of MySQLadmin shutdown
SUPER Enables use of CHANGE MASTER, KILL, PURGE MASTER LOGS, and SET GLOBAL statements, the MySQLadmin debug command; allows you to connect (once) even if max_connections is reached
UPDATE Enables use of UPDATE
USAGE Synonym for privileges
GRANT OPTION Enables privileges to be granted