Today I am presenting a comfortable position to automate database saving nosri mysql database, in addition to the original script can be added at the end of the rescue and compression of individual databases in few lines the copy on a remote server and a message box in our report email.

#! / bin / bash
# Shell script to backup MySql database
# To backup databases Nysql files to / backup dir and later pick up by your
# Script. You can skip few databases from backup too.
# For more info please see (Installation info):
# Http://www.cyberciti.biz/nixcraft/vivek/blogger/2005/01/mysql-backup-script.html
# Last updated: Aug - 2005
# -----------------------
# This is a free shell script under GNU GPL version 2.0 or above
# Copyright (C) 2004, 2005 nixCraft project
# Feedback / comments / suggestions: http://cyberciti.biz/fb/
# -------------------------
# This script is part of nixCraft shell script collection (NSSC)
Visit # http://bash.cyberciti.biz/ for more information.
# Updated Sep - 2007 by CyberTek - http://www.cybertek.it
# -------------------------

Myuser = "SET-MYSQL-USER-NAME" # USERNAME
Mypass = "SET-PASSWORD" # PASSWORD
MyHOST = "localhost" # Hostname

# Linux bin paths, change this if it can not be autodetected via which command
MYSQL = "$ (which mysql)"
Mysqldump = "$ (which mysqldump)"
Chown = "$ (which chown)"
CHMOD = "$ (which chmod)"
GZIP = "$ (which gzip)"

# Backup Dest directory, change this if you have someother location
DEST = "/ backup"

# Main directory where backups will be stored
MBD = "$ DEST / mysql"

# Get hostname
HOST = "$ (hostname)"

# Get data in dd-mm-yyyy format
NOW = "$ (date +"% d -% m -% Y ")"

# File to store current backup file
FILE = ""
# Store list of databases
DBS = ""

# DO NOT BACKUP these databases
IGGY = "test"

[- D $ MBD] & & mkdir - p $ MBD | |:

# Only root can access it!
Chown $ 0.0 - R $ DEST
$ CHMOD 0600 $ DEST

# Get all database list first
DBS = "$ ($ MYSQL-u myuser-h $ $ $ MyHOST-p mypass-BSE 'show databases')"

for db in $ DBS
do
skipdb = - 1
if [ "$ IGGY"! = ""];
then
for i in $ IGGY
do
[ "$ Db" = = "$ i"] & & skipdb = 1 | |:
done
florence

if [ "$ skipdb" = = "-1"]; then
FILE = "$ MBD / $ db. $ HOST. NOW.gz $"
# Do all InOne job in pipe,
# Connect to mysql using mysqldump for select mysql database
# And pipe it out to gz file in backup dir:)
$ Mysqldump - u myuser $ - $ MyHOST h - p $ mypass $ db | $ GZIP - 9> $ FILE
florence

done

# Copy on remote server
echo'******************* Example_Backup *******************'> / root / mybackup.txt
echo \>> / root / mybackup.txt
date>> / root / mybackup.txt
echo \>> / root / mybackup.txt
'********************* Details *********************' echo>> / root / mybackup.txt
# remember that the scp command you can not specify the password if you have cleared a key on the target system
# otherwise you will have to do
scp / backup / mysql / * root@example.dom: / home / backup / example / mysql /
oh-ls / backup / mysql />> / root / mybackup.txt
echo'************************************************ * '>> / root / mybackup.txt
# send an email with the report of the work performed
mail-s "Example Backup" example@example.dom </ root / mybackup.txt

# remove after upload
rm-f / backup / mysql / *

Of course, the product is not yet in position to compete with potential phpMyAdmin, but voltela simplicity and lightness nostromotivo could be the choice. If we intragrare a mysql client via web maybe we can turn to this product. To go to the site of sidu click here.

• Sorting and filtering of database tables.
• Possibility to connect to hosts and local and remote databases.
• Secure access to prevent unauthorized access to data.
• Working with users and multiple simultaneous sessions.
• Caching db and tables to enable a fast display.

No time to talk about how to create tables optimized to give better results. I remember only that the fields indexed improve much the execution time. Talk instead of some little trick to make more efficient MySQL which usually comes installed on most distro without too many parameters for tuning. The info refer a Linux distro but can be easily adapted to other OS.

The configuration file is usually in / etc / my.ini.

Eempio of my.ini:

[mysqld]
datadir = / var / lib / mysql
socket = / var / lib / mysql / mysql.sock
set-variable = key_buffer = 64M
set-variable = max_user_connections = 100
set-variable = query_cache_size = 64M
# Default to using old password format for compatibility with mysql 3.x
# Clients (those using the mysqlclient10 compatibility package).
old_passwords = 1

[mysql.server]
user = mysql
basedir = / var / lib

[mysqld_safe]
err-log = / var / log / mysqld.log
pid-file = / var / run / mysqld / mysqld.pid

buffers: the buffer of any incremeto will get a database much faster. Then set the buffers based on the amount of memory available, taking into account the consumption made by the operating system and other applications and services in use. If you allocate too much memory while not having a provision may run into overall performance of the system.

Wait_timeout: represents the time that elapses before it is closed automatically connect to MySQL because of inaction. Lower number is better performance. In this case you must also play the kind of connections and their alleged time to calibrate the best parameter.

back_log: controls how many simultaneous connections it can sustain while Mysql creates new thread. The lower the number, the higher the speed. Beware, it's easy to not degrade the performance by setting this parameter correctly, use this only where absolutely necessary.

Max_user_connections: Sets the maximum number of connections allowed to a user. It is very useful in systems where the same server MySQL users have access simultaneously.

Delayed_queue_size: number of rows along with INSERT DELAYED. Less will be along the lines, the faster Mysql.

Sort_buffer_size: sets the size of the buffer allocated to the implementation of ORDER BY clauses and GROUP BY clauses. To make queries more efficient it set this parameter to a value greater than 2M.

Table_cache: controls the number of open tables for all threads, increasing this variable will increase the tables loaded simultaneously and the execution of Mysql will be much faster. Attention to this parameter, the operating systems have a limit of pointers to files opened at once, if you exceed this limit you may incur in any system malfunction.

Thread_cache_size: Sets the number of threads to keep in cache. This feature makes the use of threads very fast.

The SHOW STATUS command helps us to monitor in real time those variables, which more than others to help us understand what are the most significant improvements we can make:

• Handler_read_first: Indicates how many times the first record was read from a table. If this happens frequently, it means that the table was not indexed properly.
• Innodb_buffer_pool_wait_free: indicates the number of times that MySQL has to wait for the flushing of pages of memory. If the return value is high then the buffer pool size has not been configured correctly for the amount of write operations undergone by the server.
• Key_reads: indicates the number of times that MySQL access the file system for database indexes. This slows down the execution speed of queries, so if this value is high then it means that the key buffer is too small and should be increased.
• Max_used_connections: Indicates the maximum number of connections that MySQL had to open during the last boot. On the one hand this is a true benchmark (it helps to decide the maximum number of connections that the server can support) and the other is presented as a convenient traffic analyzer.
• Open_tables: Indicates the current number of tables open. This value should be analyzed in combination with the cache size of the table. Normally the size of table_cache should be set higher for this variable. If the value of open_tables is much lower than table_cache to avoid performance problems, it is therefore also reduce the size of the cache. If the value of open_tables is very high and is approaching the size of the cache, then raise it table_cache.
• Select_full_join: The number of full join MySQL has performed to meet the demands of the client. A high value indicates that instead of using indexes, MySQL has been forced to perform full join. This suggests the need for a strong index of these tables.
• Slow_queries: indicates the number of queries that took more time than usual to be done. A high value should lead us to analyze the Slow Query Log to identify what are the slow query to optimize.
• Threads_connected: Indicates the total number of clients that are connected to the server in such a moment.
• Created_tmp_disk_tables: indicates the number of temporary tables, which were created on disk instead of memory. Since access to the tables on a disk is usually slower than in memory, the contents of the variable will be inversely proportional to the speed with which queries are running the command CREATE TEMPORARY TABLE.
• Uptime: Displays how many seconds is on the service.