Category: Mysql/Mariadb

我不擅长数据库优化，但是今天在stackoverflow上看到有perl脚本来提供优化的suggestion，真心不错

这个脚本的名字叫MySqlTuber

The MySQLTuner script assesses your MySQL installation, and then outputs suggestions for increasing your server’s performance and stability.

Download the MySQLTuner script:

wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl

Change the scripts permissions to be executable:

chmod +x mysqltuner.pl

Run the mysqltuner.pl script. You will be prompted to enter in your MySQL administrative login and password:

./mysqltuner.pl

The script will return results similar to the output below:

>> MySQLTuner 1.4.0 - Major Hayden <[email protected]>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password:
[OK] Currently running supported MySQL version 5.5.41-0+wheezy1
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
[--] Data in InnoDB tables: 1M (Tables: 11)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 11

-------- Security Recommendations -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 47s (113 q [2.404 qps], 42 conn, TX: 19K, RX: 7K)
[--] Reads / Writes: 100% / 0%
[--] Total buffers: 192.0M global + 2.7M per thread (151 max threads)
[OK] Maximum possible memory usage: 597.8M (60% of installed RAM)
[OK] Slow queries: 0% (0/113)
[OK] Highest usage of available connections: 0% (1/151)
[OK] Key buffer size / total MyISAM indexes: 16.0M/99.0K
[!!] Query cache efficiency: 0.0% (0 cached / 71 selects)
[OK] Query cache prunes per day: 0
[OK] Temporary tables created on disk: 25% (54 on disk / 213 total)
[OK] Thread cache hit rate: 97% (1 created / 42 connections)
[OK] Table cache hit rate: 24% (52 open / 215 opened)
[OK] Open file limit used: 4% (48/1K)
[OK] Table locks acquired immediately: 100% (62 immediate / 62 locks)
[OK] InnoDB buffer pool / data size: 128.0M/1.2M
[OK] InnoDB log waits: 0
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Enable the slow query log to troubleshoot bad queries
Variables to adjust:
query_cache_limit (> 1M, or use smaller result sets)

MySQLTuner offers suggestions regarding how to better the database’s performance. If you are wary about updating your database on your own, following MySQLTuner’s suggestions is one of the safer ways to improve your database performance.

当你要导入一个比较大的mysql数据库的时候，可能会产生这个错误:

mysql> source file.sql
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 2
Current database: *** NONE ***

ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 3
Current database: *** NONE ***

这是因为max_allowed_packet设置的太小了，再/etc/my.cnf中，将这个参数设置为64M或者根据实际情况设为更大的值就可以了

max_allowed_packet=64M

mysql文档:

https://dev.mysql.com/doc/refman/5.5/en/replication-features-max-allowed-packet.html

shell> mysqldump db1 > dump.sql
shell> mysqladmin create db2
shell> mysql db2 < dump.sql

Do not use –databases on the mysqldump command line because that causes USE db1 to be included in the dump file, which overrides the effect of naming db2 on the mysql command line.

有的时候root的密码不为空，这个时候我们就需要:

shell> mysqldump -uroot -p db1 > dump.sql

这个我们就可以把db1的表给倒出来了