MySQL Table cleanup

MySQL Table Cleanup

## This script will  is usefull for cleanup the table
# 1. rename table customer_table to customer_table_currentdate
# 2. create table customer_table with schema customer_table_currentdate
# 3. Copy 1 month data to customer_table from customer_table_currentdate

mysql -uprabhat -p'ab7^44' -e " use customermgt;  rename table customer_table to customer_table_`date '+%Y%m%d'`; create table customer_table like customer_table_`date '+%Y%m%d'`; INSERT INTO customer_table SELECT * FROM customer_table_`date '+%Y%m%d'`  where DATE(starttime) > '`date --date="1 months ago" +%Y-%m-%d`';" ;

mysqldump with where caluse

mysqldump  with where caluse,  selectively exporting data that matches the conditions.

One of this features of the MySQL mysqldump utility is to adapt the individual queries in order to limit the rows selected in the backup. This is achieved with the “-w” or “–where” which appends the same WHERE clause on each of the tables.

e.g.

mysqldump -uroot db_customer customer_table -w "date(cust_insert_time) < '2013-06-01 00:00:00' " --opt > db_db_customer customer_table_beforejun1.sql

or

mysqldump -uroot --no-create-info db_customer customer_table -w "date(cust_insert_time) < '2013-06-01 00:00:00' " --opt > db_db_customer customer_table_beforejun1.sql

 

MySQL User Privileges Migration

During the MySQL version upgrade or for easy user backup you can you below.
 
To export all MySQL user privileges run following script.
  
mysql -h {host_name} -u {user_name} -p{password} -Ne "select distinct concat( \"SHOW GRANTS FOR '\",user,\"'@'\",host,\"';\" ) from user;" mysql | mysql -h {host_name} -u {user_name} -p{password} | sed 's/\(GRANT .*\)/\1;/;s/^\(Grants for .*\)/## \1 ##/;/##/{x;p;x;}'

Note : Replace {host_name}, {user_name} and {password} with your values.

Above script will generate all grants statements.You can then take that output and run the statements against MySQL on the new server.