Linux Ask! is a Q & A web site specific for Linux related questions. Questions are collected, answered and audited by experienced Linux users.
How to connect MySQL without using password
Answer:
1. Create a .my.cnf in your home folder
# vi ~/.my.cnf
2. Enter your MySQL login information and save the file.
[client]
user=john
password=john_password
3. Try typing mysql in shell and you should able to login automatically.
How to rename database in MySQL
Answer:
If you are using MyISAM storage engine, the easiest way is to take down the MySQL server and rename the database folder name.
Else, you can use the mysqldump tool, steps as following:
1. Export the database using mysqldump tool
# mysqldump -u root -p old_db > old_db.sql
2. Create a new database
# mysql -u root -p -e 'create database new_db'3. Finally import into the new database
# cat old_db.sql |mysql -u root -p new_dbDisable DNS hostname lookup in MySQL
Answer:
If you never use MySQL authentication by hostname, it is better to disable the DNS hostname lookup for better performance. Especially if you are always connecting from localhost, or authenticate remote host by IP address)
To disable this feature, edit the MySQL configurations, e.g. /etc/my.cnf
[mysqld]
...
...
skip-name-resolve
Add the "skip-name-resolve" will do the tricks for you.
Don't forget to restart MySQL to take effect.
# /sbin/service mysqld restart
How to search man pages by keyword
Answer:
Sometimes, you want to search for all commands related to mysql, for example
You can use the following command
# man -k mysql
Bundle::DBD::mysql (3pm) - A bundle to install Perl drivers for MySQL
DBD::mysql (3pm) - MySQL driver for the Perl5 Database Interface (DBI)
DBD::mysql::INSTALL (3pm) - How to install and configure DBD::mysql
innotop (1) - MySQL and InnoDB transaction/status monitor.
msql2mysql (1) - convert mSQL programs for use with MySQL
mysql (1) - the MySQL command-line tool
mysql_client_test (1) - test client API
mysql_client_test_embedded (1) - test client API for embedded server
mysql_config (1) - get compile options for compiling clients
...
All the related man pages will be shown.
How to dump an MySQL/InnoDB database
Answer:
To do a point-in-time SQL dump of a InnoDB database, use the mysqldump command with the --single-transaction flag
# mysqldump --master-data=2 --single-transaction huge_db > backup.sql
The binlog position will be written in the backup.sql, which is very useful for database recovery if needed.
