mysql user copy/migration

gensqluser.sh  : this script will gen sql statement for creating new user
---
mygrants()
{
  mysql -B -N $@ -e "SELECT DISTINCT CONCAT(
    'SHOW GRANTS FOR ''', user, '''@''', host, ''';'
    ) AS query FROM mysql.user" | \
  mysql $@ | \
  sed 's/\(GRANT .*\)/\1;/;s/^\(Grants for .*\)/## \1 ##/;/##/{x;p;x;}'
}

mygrants --host=localhost --user=username--password=password
---

host1# ./gensqluser.sh >> user.sql

host2# mysql -u -p < user.sql

mysql commonly use statement

Create table

mysql> CREATE  table if not exists tblname like old_tblname;

INSERT ... SELECT

mysql> INSERT INTO tblname1 SELECT *  from tblname2 where ...;

Empty table

mysql> TRUNCATE table story_longcon_40;

Delete/Drop table

mysql> DROP TABLE tblname; 

Delete/Drop database

mysql> DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

Optimize Table

mysql> OPTIMIZE [NO_WRITE_TO_BINLOG | LOCAL] TABLE tbl_name [, tbl_nam

Delete binary logs 
This will delete the binary log till master.000080

mysql> purge binary logs to 'master-bin.000081';

Start slave

mysql> START SLAVE [thread_type [, thread_type] ... ]

or

mysql> START SLAVE [SQL_THREAD] UNTIL
MASTER_LOG_FILE = 'log_name', MASTER_LOG_POS = log_pos

[MySQL] Backing up data

There are several ways to backing up data
1) Logical backup

* SQL dumps

#mysqldump dbname tblname

Not suitable for huge backup. Both table structure and the data are stored together.(option available)

* Delimited file backups
backing up :

mysql> SELECT * INTO OUTFILE '/tmp/t1.txt'
    -> FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    -> LINES TERMINATED BY '\n'
    -> FROM test.t1

restore :

mysql> LOAD DATA INFILE '/tmp/t1.txt'
    -> INTO TABLE test1.t1
    -> FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    -> LINES TERMINATED BY '\n';

*parallel dump: maatkit(mk-parallel-dump)

2) File system snapshot (LVM)
not covered here.

mysql backup/dump/export table schema, definition

1) use mysqldump

server# mysqldump -uroot -p dbname tblname -d 

with this method you will get sql statement. You can use this for backing up table definition as well.

2) create table from another table

mysql> create table if not exists tblname like old_tblname;

with this method you get a copy of table. It is useful if you want to have a another table with the same structure for backing up, testing.

mysql open query log without changing my.cnf and restart

Use mysql client connect to server

#show variables like 'general%';

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

| Variable_name | Value |

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

| general_log | OFF |

| general_log_file | /var/run/mysqld/mysqld.log |

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

This 2 vaiables control mysql query logging.

To turn query log on :

SET GLOBAL general_log_file = '/var/log/mysql/sql-log.log' ;

SET GLOBAL general_log = 1

To turn query log off :

SET GLOBAL general_log = 0 ;

SET GLOBAL general_log_file = '/var/run/mysqld/mysqld.log';

Notice: This is for mysql 5.1 only. For mysql 5.0, those variable is not available. you need editing my.cnf and restart.

monitoring mysql with cacti

By default, cacti don't come with mysql graphing template. Here is the plugin that help you graphing mysql important data.

The installation is like the other plugins.
1) copy php script to cacti host /script directory
2) import template using web interface
3) add mysql user to mysql host
4) have fun :)


plugin document/download :
http://code.google.com/p/mysql-cacti-templates/wiki/InstallingTemplates

author blog:
http://www.xaprb.com/blog/2009/10/25/version-1-1-4-of-improved-cacti-templates-released/