RESTful web services mysql-udf-http

wget http://curl.haxx.se/download/curl-7.21.1.tar.gz tar zxvf curl-7.21.1.tar.gz
cd curl-7.21.1/
./configure --prefix=/usr
make && make install
cd ../
echo "/usr/local/webserver/mysql/lib/mysql/" > /etc/ld.so.conf.d/mysql.conf
/sbin/ldconfig
wget https://storage.googleapis.com/google-code-archive-downloads/v2/code.google.com/mysql-udf-http/mysql-udf-http-1.0.tar.gz
tar zxvf mysql-udf-http-1.0.tar.gz
cd mysql-udf-http-1.0/
./configure --prefix=/usr/local/webserver/mysql --with-mysql=/usr/local/webserver/mysql/bin/mysql_config
make && make install
cd ../

2 – Add the UDF in MySQL.
create function http_get returns string soname 'mysql-udf-http.so';
create function http_post returns string soname 'mysql-udf-http.so';
create function http_put returns string soname 'mysql-udf-http.so';
create function http_delete returns string soname 'mysql-udf-http.so';

Rsolved ERROR 1290 (HY000): INTO OUTFILE CSV FILE

Rsolved ERROR 1290 (HY000): INTO OUTFILE CSV FILE
The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

Find out where you are allowed to write

mysql> SELECT @@GLOBAL.secure_file_priv;

+---------------------------+

| @@GLOBAL.secure_file_priv |

+---------------------------+

| NULL                      |

+---------------------------+

1 row in set (0.00 sec)

Enable read/write for MySQL installed

stop mysql Server

vi my.cnf 

[mysqld_safe]

[mysqld]

secure_file_priv="/tmp/"

start mysql

Log in mysql

mysql> SELECT @@GLOBAL.secure_file_priv;

+---------------------------+

| @@GLOBAL.secure_file_priv |

+---------------------------+

| /tmp/          |

+---------------------------+

1 row in set (0.00 sec)

Finally exporting  into a CSV file

mysql> SELECT * FROM testeble INTO OUTFILE '/tmp/test.csv' FIELDS TERMINATED BY ',';

Query OK, 9901 rows affected (1.65 sec)

mysql>

Solved ERROR 2027 (HY000): Malformed packet

Unable to connect mysql server remotely ,The connection seems OK
it might because of mysql client bug 

You need to update or downgrade the MySQL Client 
You must upgrade the "old_password" hashed password:

Solved ERROR 2049 (HY000): Connection using old (pre-4.1.1) authentication protocol refused (client option 'secure_auth' enabled)

A error occured when run `mysql` from the terminal to connect to a server.

Add `--skip-secure-auth` to the end of the statement
mysql -u repluser -p'myreplpass'  -hxx.xx.xx.xx --skip_secure_auth

MySQL access and replication blocked by secure_auth
Connection using old (pre-4.1.1) authentication protocol refused (client option 'secure_auth' enabled)

I have  tried to connect to a MySQL database and you see this error
On master server

select Password from mysql.user;
SELECT @@session.old_passwords, @@global.old_passwords;

+-------------------------+------------------------+
| @@session.old_passwords | @@global.old_passwords |
+-------------------------+------------------------+
|                       1 |                      1 |
+-------------------------+------------------------+
1 row in set (0.00 sec)

SET @@session.old_passwords = 0;
Query OK, 0 rows affected (0.00 sec)

GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'xx.xx.xx.xx' IDENTIFIED BY 'myreplpass';
Query OK, 0 rows affected (0.00 sec)

try set strong password of replication user

mysql flush commands

List of all mysqladmin flush commands.

flush-hosts: Flush all information in the host cache.
flush-privileges: Reload the grant tables (same as reload).
flush-status: Clear status variables.
flush-threads: Flush the thread cache.

# mysqladmin -u root -pmypassword flush-hosts
# mysqladmin -u root -pmypassword flush-logs
# mysqladmin -u root -pmypassword flush-privileges
# mysqladmin -u root -pmypassword flush-status
# mysqladmin -u root -pmypassword flush-tables
# mysqladmin -u root -pmypassword flush-threads

Check MySQL Server is up and running

# mysqladmin -u root -p ping
Enter password:
mysqld is alive

Mysql dba tips and tricks: ERROR 1044 (42000): Access denied for user Restor...

Mysql dba tips and tricks: ERROR 1044 (42000): Access denied for user Restor...: Access denied for user 'root'@'%' I am able to login fine :  mysql -u root -p mysql>  GRANT SELECT, LOCK TABLES,EXE...

ERROR 1044 (42000): Access denied for user Restore the MySQL root user’s full privileges?

Access denied for user 'root'@'%'

I am able to login fine :
 mysql -u root -p

mysql>  GRANT SELECT, LOCK TABLES,EXECUTE  ON `dbname`.* TO 'dbuser'@'xx.xx.xx.xx' IDENTIFIED BY '[password]' WITH GRANT OPTION;
ERROR 1044 (42000): Access denied for user 'dbuser'@'xx.xx.xx.xx' to database 'dbanme'


SELECT `User`, `Grant_priv` FROM `mysql`.`user` WHERE `User` = 'root';

You will probably notice it returns a 'N' for Grant_priv. So do this:

UPDATE `mysql`.`user` SET `Grant_priv` = 'Y' WHERE `User` = 'root';
FLUSH PRIVILEGES;
SELECT `User`, `Grant_priv` FROM `mysql`.`user`;

How to understand the MySQL Slow Query Log

How to understand the MySQL Slow Query Log

root@server#tail -f /var/log/mysql_slow_query.log
# Time: 2017-04-09T20:10:09.472872Z
# User@Host: db_user[db_name] @  [x.x.x.125]
# Query_time: 3.776369  Lock_time: 0.000269 Rows_sent: 0  Rows_examined: 308448
SET timestamp=1491768609;
insert into tempo_table select * from tempo_table_tmp;

Let us understand what each line means:

• The first line shows the time the query was logged. The format is YYMMDD H:M:S. We can see the query above was logged at 2017 April 9th at 20:10 - NOTE: This is server time, which may be different from your local time
• Next, we'll see the MySQL user, database, and hostname/ip address
• The third line shows the total query time, the Lock time, the number of Rows "sent" or returned, and the number of rows examined during the query.
• Below that you'll see SET timestamp=UNIXTIME; < this is the time the query actually happened. It is good to check that you're not looking at a problem that happened months ago if you're trying to figure out why something is slow right now. I'll explain how to turn that into a usable time below
• The final line shows the full query.

To get a human-readable timestamp from the Unix Epoch,

root@server# date -d @1491768609
Mon Apr 10 01:40:09 IST 2017

Counting the number of sleeping connections

INFORMATION_SCHEMA, For instance, counting the number of sleeping connections can be done with:

mysql> SELECT COMMAND,COUNT(*) TOTAL FROM INFORMATION_SCHEMA.PROCESSLIST GROUP BY COMMAND ORDER BY TOTAL DESC;

+---------+-------+

| COMMAND | TOTAL |

+---------+-------+

| Sleep   |    36 |

| Query   |     1 |

+---------+-------+

2 rows in set (0.01 sec)

mysql> SELECT COUNT(*) FROM INFORMATION_SCHEMA.PROCESSLIST WHERE COMMAND='Sleep';
+----------+
| COUNT(*) |
+----------+
|       36 |
+----------+
1 row in set (0.00 sec)

Set up and verify automated MySQL backup in 15 minutes

The Solution: Zmanda Recovery Manager for MySQL
Zmanda Recovery Manager for MySQL (ZRM for MySQL) simplifies this essential part of
database administration with an easy-to-use yet powerful framework for scheduling and
monitoring backups and restores.

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'
• Grant access to 'backup-user' in mysql with password 'pass123'

mysql> grant select,show view, create view, 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 |
mysql> flush privileges;

ZRM 2.1 for MySQL also requires perl-DBI as well as perl-XML-parser. For local raw
backups, the perl-DBD-MySQL package is also required. These perl modules must be
installed before ZRM 2.1 for MySQL installation. These perl modules are available in all
Linux distributions (might be already installed). Packages are also available for these
perl modules in the following locations (choose the package based on your distribution)
perl-DBI, perl-XML-parser, and perl-DBD-MySQL.
TIP: You can copy and paste from all of the examples here, editing as appropriate 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-2.1-1 (http://www.zmanda.com/download-zrm.php)

2. Install the ZRM for MySQL 2.1 community edition.
Installation of the package must be performed as root.
3. Verify ZRM for MySQL Installation.
ZRM for MySQL executables are located in /usr/bin:
[root@fc8-build ~]# rpm -ivh MySQL-zrm-2.1-1.noarch.rpm --nodeps
warning: MySQL-zrm-2.1-1.noarch.rpm: Header V3 DSA signature: NOKEY, key ID 3c5d1c92
Preparing... ########################################### [100%]
1:MySQL-zrm ########################################### [100%]

[root@fc8-build ~]# ls -lh /usr/bin/mysql-zrm*
-rwxr-x--- 1 mysql mysql 3.1K Nov 19 17:14 /usr/bin/mysql-zrm
-rwxr-x--- 1 mysql mysql 1.7K Nov 19 17:14 /usr/bin/mysql-zrm-abort-backup
-rwxr-x--- 1 mysql mysql 57K Nov 19 17:14 /usr/bin/mysql-zrm-backup
-rwxr-x--- 1 mysql mysql 1.6K Nov 19 17:14 /usr/bin/mysql-zrm-check
-rwxr-x--- 1 mysql mysql 2.8K Nov 19 17:14 /usr/bin/mysql-zrm-extract-backup
-rwxr-x--- 1 mysql mysql 1.8K Nov 19 17:14 /usr/bin/mysql-zrm-getconf
-rwxr-x--- 1 mysql mysql 4.4K Nov 19 17:14 /usr/bin/mysql-zrm-list
-rwxr-x--- 1 mysql mysql 11K Nov 19 17:14 /usr/bin/mysql-zrm-manage-backup
-rwxr-x--- 1 mysql mysql 2.7K Nov 19 17:14 /usr/bin/mysql-zrm-migrate-file-ownership
-rwxr-x--- 1 mysql mysql 7.1K Nov 19 17:14 /usr/bin/mysql-zrm-parse-binlogs
-rwxr-x--- 1 mysql mysql 4.7K Nov 19 17:14 /usr/bin/mysql-zrm-purge
-rwxr-x--- 1 mysql mysql 27K Nov 19 17:14 /usr/bin/mysql-zrm-reporter
-rwxr-x--- 1 mysql mysql 20K Nov 19 17:14 /usr/bin/mysql-zrm-restore
-rwxr-x--- 1 mysql mysql 13K Nov 19 17:14 /usr/bin/mysql-zrm-scheduler
-rwxr-x--- 1 mysql mysql 8.0K Nov 19 17:14 /usr/bin/mysql-zrm-verify-backup
-rwxr-x--- 1 mysql mysql 13K 2008-04-07 13:40 /usr/bin/mysql-zrm-scheduler
-rwxr-x--- 1 mysql mysql 7.7K 2008-04-07 13:40 /usr/bin/mysql-zrm-verify-backup

ZRM for MySQL configuration files are located in /etc/mysql-zrm:
[root@fc8-build ~]# ls -lh /etc/mysql-zrm/
total 24K
-rwxrwx--- 1 mysql mysql 11K Nov 19 17:14 mysql-zrm.conf
-rwxrwx--- 1 mysql mysql 46 Nov 19 17:14 mysql-zrm-release
-rwxrwx--- 1 mysql mysql 3.1K Nov 19 17:14 mysql-zrm-reporter.conf
-rw-rw---- 1 mysql mysql 596 Nov 19 17:14 RSS.header

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-2.1


Configure ZRM for MySQL to backup your database
In the example, we back up a database called sakila . The sakila database includes 23
ta[broleost@. fc8-build mysql]# mysql -u backup-user -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.0.45 Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> use sakila;
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_sakila |
+----------------------------+
| actor |
| actor_info |
| address |
| category |
| city |
| country |
| customer |
| customer_list |
| film |
| film_actor |
| film_category |
| film_list |
| film_text |
| inventory |
| language |
| nicer_but_slower_film_list |
| payment |
| rental |
| sales_by_film_category |
| sales_by_store |
| staff |
| staff_list |
| store
+----------------------------+
23 rows in set (0.00 sec)

1. Log in as root on your Linux Server.
2. Configure a daily full backup of the sakila database.

For the example, we will create a directory called dailyrun, and copy the sample mysqlzrm.
conf configuration file to that location. This allows more flexibility to create individual
backup sets for individual databases.
3. Edit /etc/mysql-zrm/dailyrun/mysql-zrm.conf to change the parameters as
shown below.
Please be sure to secure this file with the proper permissions, as it stores the password for
the MySQL backup user in clear text.
The example demonstrates a full backup:
[root@fc8-build /]# cd /etc/mysql-zrm
[root@fc8-build /]# mkdir dailyrun
[root@fc8-build /]# cp mysql-zrm.conf dailyrun/
[root@fc8-build /]# cd dailyrun/
[root@fc8-build dailyrun]# ls -lh
total 12K

-rwxr-x--- 1 root root 11K Dec 4 21:05 mysql-zrm.conf

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

The example demonstrates 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:
The example retains backups for 10 days:
Compressing the backups saves disk space:
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

The example retains 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

Compressing the backups saves 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

This portion of the example selects the sakila database for backup:
This part of the configuration file stores the MySQL username abd password that created
above:
(the password below is stored in clear text)
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=”sakila”

This part of the configuration file stores the MySQL username abd password that 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"

Verbose mode can be disabled once MySQL is configured and verified:
If your Linux server can send mail, you can configure MySQL for ZRM to send backup
reports to a valid email address):
Save and close the /etc/mysql-zrm/dailyrun/mysql-zrm.conf file.
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

If your Linux server can send mail, you can configure MySQL for ZRM to send backup
reports to 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="dba@yourcompany.com"

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

Perform a Backup
These steps must be peformed as the mysql user.
1.On your MySQL Server, become user mysql to run ZRM for MySQL
and start the dailyrun backup on the sakila database.
2. ZRM for MySQL will output the following report to screen.
[root@fc8-build ~]# su - mysql
[mysql@fc8-build ~]$ mysql-zrm-backup --backup-set dailyrun

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

backup:INFO: ZRM for MySQL Community Edition - version 2.1
dailyrun:backup:INFO: START OF BACKUP
dailyrun:backup:INFO: PHASE START: Initialization
dailyrun:backup:WARNING: last backup directory is not valid
dailyrun:backup:INFO: Mail address: dba@yourcompany.com is ok
dailyrun:backup:INFO: ZRM Temporary configuration file = /etc/mysql-zrm/dailyrun/tmprBrSY.conf
dailyrun:backup:INFO: {
dailyrun:backup:INFO: verbose=1
dailyrun:backup:INFO: retention-policy=10D
dailyrun:backup:INFO: backup-level=0
dailyrun:backup:INFO: mailto=dba@yourcompany.com
dailyrun:backup:INFO: databases=sakila
dailyrun:backup:INFO: backup-mode=logical
dailyrun:backup:INFO: password=******
dailyrun:backup:INFO: compress=
dailyrun:backup:INFO: user=backup-user
dailyrun:backup:INFO: }
dailyrun:backup:INFO: Getting mysql variables
dailyrun:backup:INFO: mysqladmin --user="backup-user" --password="*****" variables
dailyrun:backup:INFO: datadir is /var/lib/mysql/

dailyrun:backup:INFO: mysql_version is 5.0.45

dailyrun:backup:WARNING: Binary logging is off.
dailyrun:backup:INFO: InnoDB data file are /var/lib/mysql/ibdata1
dailyrun:backup:INFO: InnoDB log dir is /var/lib/mysql/.
dailyrun:backup:INFO: backup set being used is dailyrun
dailyrun:backup:INFO: backup-set=dailyrun
dailyrun:backup:INFO: backup-date=20081204022708
dailyrun:backup:INFO: mysql-server-os=Linux/Unix
dailyrun:backup:INFO: host=localhost
dailyrun:backup:INFO: backup-date-epoch=1208856428
dailyrun:backup:INFO: retention-policy=10D
dailyrun:backup:INFO: mysql-zrm-version=ZRM for MySQL Community Edition - version 2.1
dailyrun:backup:INFO: mysql-version=5.0.45
dailyrun:backup:INFO: backup-directory=/var/lib/mysql-zrm/dailyrun/20081204022708
dailyrun:backup:INFO: backup-level=0
dailyrun:backup:INFO: backup-mode=logical
dailyrun:backup:INFO: PHASE END: Initialization
dailyrun:backup:INFO: PHASE START: Running pre backup plugin
dailyrun:backup:INFO: Executing pre-backup-plugin
dailyrun:backup:INFO: PHASE END: Running pre backup plugin
dailyrun:backup:INFO: PHASE START: Flushing logs
dailyrun:backup:INFO: Flushing the logs
dailyrun:backup:INFO: mysqladmin --user="backup-user" --password="*****" flush-logs
dailyrun:backup:INFO: Getting master logname using command mysql --user="backup-user" --
password="*****" -e "show master status"
dailyrun:backup:INFO: PHASE END: Flushing logs
dailyrun:backup:INFO: PHASE START: Creating logical backup
dailyrun:backup:INFO: Command used for logical backup is mysqldump --opt --extended-insert --
single-transaction --create-options --default-character-set=utf8 --user="backup-user" --
password="*****" --databases sakila > "/var/lib/mysql-zrm/dailyrun/20081204022708/backup.sql"
dailyrun:backup:INFO: Logical backup done for the following database(s)
sakila
dailyrun:backup:INFO: logical-databases=sakila
dailyrun:backup:INFO: PHASE END: Creating logical backup
dailyrun:backup:INFO: PHASE START: Calculating backup size & checksums
dailyrun:backup:INFO: backup-size=3.22 MB
dailyrun:backup:INFO: PHASE END: Calculating backup size & checksums
dailyrun:backup:INFO: PHASE START: Compression/Encryption
dailyrun:backup:INFO: Compressing backup
dailyrun:backup:INFO: Command used is 'tar --same-owner -cpsC "/var/lib/mysqlzrm/
dailyrun/20080422022708" --exclude=backup-data --exclude=index --exclude=zrm_checksum --
exclude=backup-sql . 2>/tmp/G25pkx1RIq | gzip 2>/tmp/amXpQPUokv > "/var/lib/mysqlzrm/
dailyrun/20081204022708/backup-data" 2>/tmp/agPHdeKqBA'
dailyrun:backup:INFO: compress=
dailyrun:backup:INFO: backup-size-compressed=0.67 MB
dailyrun:backup:INFO: Removing all of the uncompressed/unencrypted data
dailyrun:backup:INFO: PHASE END: Compression/Encryption
dailyrun:backup:INFO: read-locks-time=00:00:01
dailyrun:backup:INFO: flush-logs-time=00:00:00
dailyrun:backup:INFO: compress-encrypt-time=00:00:01
dailyrun:backup:INFO: backup-time=00:00:01
dailyrun:backup:INFO: backup-status=Backup succeeded
dailyrun:backup:INFO: Backup succeeded
dailyrun:backup:INFO: PHASE START: Running post backup plugin
dailyrun:backup:INFO: Executing post-backup-plugin
dailyrun:backup:INFO: PHASE END: Running post backup plugin
dailyrun:backup:INFO: PHASE START: Mailing backup report
dailyrun:backup:INFO: mailing file /tmp/FNumR2o2Br
dailyrun:backup:INFO: mail command is cat "/tmp/FNumR2o2Br"|mail -s "[ZRM for MySQL Report]
backup-set dailyrun" dba@yourcompany.com
dailyrun:backup:INFO: PHASE END: Mailing backup report
dailyrun:backup:INFO: PHASE START: Cleanup
dailyrun:backup:INFO: PHASE END: Cleanup
dailyrun:backup:INFO: END OF BACKUP

Note: The schedule can be used to schedule backups as shown below.
The example schedules a daily full backup at 1am.

[mysql@fc8-build ~]$ mysql-zrm-scheduler --add --interval daily --start 01:00 --backup-level 0 --backup-set
dailyrun
schedule:INFO: ZRM for MySQL Community Edition - version 2.1
Logging to /var/log/mysql-zrm/mysql-zrm-scheduler.log
DONE
Verify that the scheduler has been configured correctly

[mysql@fc8-build ~]$ mysql-zrm-scheduler --query
schedule:INFO: ZRM for MySQL Community Edition - version 2.1
Logging to /var/log/mysql-zrm/mysql-zrm-scheduler.log

0 1 * * * /usr/bin/zrm-pre-scheduler --action backup --backup-set dailyrun --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).


[mysql@fc8-build ~]$ mysql-zrm-reporter --where backup-set=dailyrun --show backup-status-info
REPORT TYPE : backup-status-info
backup_set backup_date backup_level backup_status comment
-----------------------------------------------------------------------------------------------------------
dailyrun Thu 04 Dec 2008 02:27:08 0 Backup succeeded ----
AM PST

The first report shows us the status of backup(s) for backup set dailyrun, including backup
level, and whether the backup was full (0) or incremental (1).

[mysql@fc8-build ~]$ mysql-zrm-reporter --where backup-set=dailyrun --show backup-performance-info
REPORT TYPE : backup-performance-info
backup_set backup_date backup_level backup_size backup_size_compressed
backup_time compress_encrypt_time
---------------------------------------------------------------------------------------------------------------------------------------------
-
dailyrun Thu 04 Dec 2008 02:27:08 0 3.22 MB 0.67 MB 00:00:01 00:00:01
AM PST

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 the last
backup.
[mysql@fc8-build ~]$ mysql-zrm-reporter --where backup-set=dailyrun --show backup-status-info
REPORT TYPE : backup-status-info
backup_set backup_date backup_level backup_status comment
-----------------------------------------------------------------------------------------------------------
dailyrun Thu 04 Dec 2008 02:27:08 0 Backup succeeded ----
AM PST
[mysql@fc8-build ~]$ mysql-zrm-reporter --where backup-set=dailyrun --show backup-performance-info
REPORT TYPE : backup-performance-info
backup_set backup_date backup_level backup_size backup_size_compressed
backup_time compress_encrypt_time
---------------------------------------------------------------------------------------------------------------------------------------------
-
dailyrun Thu 04 Dec 2008 02:27:08 0 3.22 MB 0.67 MB 00:00:01 00:00:01
AM PST
[mysql@fc8-build ~]$ mysql-zrm --action verify-backup --backup-set dailyrun
verify-backup:INFO: ZRM for MySQL Community Edition - version 2.1

dailyrun:verify-backup:INFO: Verification successful

Perform a full restoration
First we will “accidentally” drop the sakila database. To do this, we must login to MySQL
as a user with root privileges. In the example, we have renamed the MySQL root user to
admin for security reasons.

[root@fc8-build /]# mysql -u admin -p
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| sakila |
| test |
+--------------------+
4 rows in set (0.01 sec)
mysql> drop database sakila;
Query OK, 23 rows affected (8.64 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
+--------------------+
3 rows in set (0.00 sec)

1. Determine which backup to restore from.

[mysql@fc8-build ~]$ mysql-zrm-reporter --show restore-info --where backup-set=dailyrun
REPORT TYPE : restore-info
backup_set backup_date backup_level backup_directory backup_status
comment
---------------------------------------------------------------------------------------------------------------------------------------------
--------
dailyrun Thu 04 Dec 2008 02:27:08 0 /var/lib/mysql-zrm/dailyrun/20081204022708
Backup succeeded ----
AM PST 08

2. Start the restore.
The example restores from the last full backup, from 2:27am on December 4th.

[mysql@fc7-build ~]$ mysql-zrm-restore --backup-set dailyrun --source-directory /var/lib/mysqlzrm/
dailyrun/20081204022708
restore:INFO: ZRM for MySQL Community Edition - version 2.1
dailyrun:restore:INFO: Mail address: dba@yourcompany.com is ok
dailyrun:restore:INFO: ZRM Temporary configuration file = /etc/mysql-zrm/dailyrun/tmpiujTu.conf
dailyrun:restore:INFO: {
dailyrun:restore:INFO: verbose=1
dailyrun:restore:INFO: retention-policy=10D
dailyrun:restore:INFO: backup-level=0
dailyrun:restore:INFO: mailto=dba@yourcompany.com
dailyrun:restore:INFO: databases=sakila
dailyrun:restore:INFO: source-directory=/var/lib/mysql-zrm/dailyrun/20081204022708
dailyrun:restore:INFO: backup-mode=logical
dailyrun:restore:INFO: password=******
dailyrun:restore:INFO: compress=
dailyrun:restore:INFO: user=backup-user
dailyrun:restore:INFO: }
dailyrun:restore:INFO: Getting mysql variables
dailyrun:restore:INFO: mysqladmin --user="backup-user" --password="*****" variables
dailyrun:restore:INFO: datadir is /var/lib/mysql/
dailyrun:restore:INFO: mysql_version is 5.0.45
dailyrun:restore:WARNING: Binary logging is off.
dailyrun:restore:INFO: InnoDB data file are /var/lib/mysql/ibdata1
dailyrun:restore:INFO: InnoDB log dir is /var/lib/mysql/.
dailyrun:restore:INFO: Command used is 'cat "/var/lib/mysql-zrm/dailyrun/20081204022708/backup-data"
| gzip -d | tar --same-owner -xpsC "/var/lib/mysql-zrm/dailyrun/20081204022708"
2>/tmp/xYAmLhRjkb'
dailyrun:restore: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 /tmp/5gD1P8RA8N;"
dailyrun:restore:INFO: Restored database(s) from logical backup: sakila
dailyrun:restore:INFO: Removing all of the uncompressed/unencrypted data
dailyrun:restore:INFO: Restore done in 19 seconds.

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

[root@fc8-build ~]# mysql -u backup-user -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 108
Server version: 5.0.45 Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| sakila |
| test |
+--------------------+
4 rows in set (0.00 sec)
mysql> use sakila;
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_sakila |
+----------------------------+
| actor |
| actor_info |
| address |
| category |
| city |
| country |
| customer |
| customer_list |
| film |
| film_actor |
| film_category |
| film_list |
| film_text |
| inventory |
| language |
| nicer_but_slower_film_list |
| payment |
| rental |
| sales_by_film_category |
| sales_by_store |
| staff |
| staff_list |
| store |
+----------------------------+
23 rows in set (0.00 sec)

Success!
In about 15 minutes, we have installed and configured a fast and reliable MySQL backup
solution, performed a backup, verified our backup, and performed a restoration. And we did
it with free, 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.