mysql> set global innodb_max_dirty_pages_pct = 0;
faster mysql shutdown
Leave a reply
Tag Archives: MySQL
mysql dump all users and privileges
Dumping:
mysql --skip-column-names -A -e"SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user<>''" | mysql --skip-column-names -A | sed 's/$/;/g' > mysql_user_grants.sql
Importing:
mysql mysql < mysql_user_grants.sql
mysql -e "flush privileges" -D mysql
extract single mysql database
mysql -D db_name-o < add_mysql_dump.sql
restore single table from mysqldump
It works:
grep -n ‘Table structure’ full_dump.sql
19:– Table structure for table `t1`
40:– Table structure for table `t2`
61:– Table structure for table `t3`
sed -n ‘40,61 p’ full_dump.sql > t2.sql
mysql restore all databases from backup
Do a mysqldump of all databases (mysqldump –event -A), procedures, triggers etc except the mysql and performance_schema databases
Drop all databases except the above 2 databases
Stop mysql
Delete ibdata1 and ib_log files
Start mysql
Restore from dump
mysql import with custom character set
mysql -u username -p –default-character-set=latin1 database < backup.sql
1064 You have an error in your SQL syntax … TYPE=MyISAM
When you importing database from MySQL4 version to new MySQL5, you can egt this kind of error. You can fix it:
grep -rl mysql4.sql * | xargs sed -i ” s/TYPE=MyISAM/ENGINE=MyISAM/g
search mysql database for some string
mysqldump DB –extended=FALSE | grep some_string | less -S
mysql whoami
select user(), current_user();
MySQL/InnoDB – ‘Unable to lock’ issue
InnoDB: Unable to lock /path/to/ibdata1, error: 11
InnoDB: Check that you do not already have another mysqld process
InnoDB: using the same InnoDB data or log files.
Upon further investigation, I found a not perfectly matched issue but does look similar: http://forums.mysql.com/read.php?22,22344,24497#msg-24497. I took a chance and did the following:
killed the lingering mysql process
mv (move) ibdata1 file to ibdata1.bad
cp -a ibdata1.bad ibdata1
restart the db server
Note: ‘-a’ argument in cp command is the same as –archive which means presereve as much as possible of the structure and attributes of the original files in the copy.
Voila! The mysql instance started a crash recovery and in the end all was good again. On the error log, here’s what I saw (recovery messages):
100825 16:58:37 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files…
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer…
100825 16:58:40 InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 8 2737988291.
InnoDB: Doing recovery: scanned up to log sequence number 8 2738024293
100825 16:58:48 InnoDB: Starting an apply batch of log records to the database…
InnoDB: Progress in percents: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
InnoDB: Apply batch completed
InnoDB: In a MySQL replication slave the last master binlog file
InnoDB: position 0 182, file name log-bin.000039
100825 16:59:12 InnoDB: Started; log sequence number 8 2738024293
mysql – Host ‘host_name’ is blocked
By default, mysqld blocks a host after 10 connection errors. You can adjust the value by setting max_connect_errors at server startup:
shell> mysqld_safe –max_connect_errors=10000 &
The value can also be set at runtime:
mysql> SET GLOBAL max_connect_errors=10000;
If you get the Host ‘host_name’ is blocked error message for a given host, you should first verify that there is nothing wrong with TCP/IP connections from that host. If you are having network problems, it does you no good to increase the value of the max_connect_errors variable.
mysql get table info
SELECT * FROM information_schema.TABLES WHERE TABLE_NAME = ‘myTableName’;
mysql #1017 – Can’t find file: (errno: 2)
Simple repair wouldn’t help, because you will get errors like: repair ‘Error Can’t open table’ or ‘error Corrupt’
cd /var/lib/mysql/database_name
touch broken_table.MYI
touch broken_table.MYD
chown mysql:mysql broken_table.*
mysql -u mysql_user -pXXXXXX -e “REPAIR TABLE broken_table USE_FRM” -B database_name
mysql get current database engines
mysql -u mysql_user -pXXXXXX -e “SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES where TABLE_SCHEMA = ‘database_name'”
mysql monitor active proccess real time
watch “mysql -e ‘show full processlist'”