Shell: 无密码读写Mysql数据库, 无密码被分导出Mysql数据库, mysqldump , Connect to MySQL Without Root Password on Terminal

设置Mysql登录信息 How to Set MySQL Root Password

一般shell连接数据库的方法是：

mysqladmin -u root password YOURNEWPASSWORD

无登录链接数据库的方法是创建或者修改 ~/.my.cnf 文件

Now create the config file ~/.my.cnf and add configurations below in it (remember to replace mysqluser and mysqlpasswd with your own values).

[mysql]
user=user
password=password

如果你还是用了mysqldump，那么可以这样：

[mysql]
user=root
password={password}
[mysqldump]
user=root
password={password}

保存之后，设置权限为 600

Save and close the file. Then set the suitable permissions on it, to make it only readable and writable by you.

sudo chmod 0600 ~/.my.cnf

现在就可以了，执行一下mysql命令验证一下：

# mysql 
# mysql -u root

如果你要备份的话，可以用下面方法：

cd ${BACKUP}

echo "----You are In Backup Directory"
sudo mv *.sql history/ > /dev/null 2>&1
sudo rm -rf *.sql > /dev/null 2>&1
echo "----Old Databases are Moved to oldbackup folder"
Now=$(date +"%Y-%m-%d--%H:%M:%S")
File=backup-$Now.sql

#### if you did not set ~/.my.cnf, you have to manaully add id and password here, but this is not safe ####
#sudo mysqldump --user=${USERNAME} --password=${PASSWORD} --default-character-set=utf8 --single-transaction ${DBNAME} > ${File}

#### create or edit your ~/.my.cnf file so safely export your databases ####
#[mysql]
#user=root
#password={password}
#[mysqldump]
#user=root
#password={password}
#### make sure your ~/.my.cnf file has a 600 permit ####
sudo mysqldump --defaults-file=${HOME}/.my.cnf -u ${USERNAME} --default-character-set=utf8 --single-transaction ${DBNAME} > ${File}

echo "----Your Database Backup Successfully Completed"

–defaults-file 这个比较重要，设置你的配置文件的路径！

附上一些简单的mysql指令：

1. How to set MySQL Root password?

# mysqladmin -u root password YOURNEWPASSWORD

2. How to Change MySQL Root password?

mysqladmin -u root -p123456 password 'xyz123'

3. How to check MySQL Server is running?

# mysqladmin -u root -p ping
Enter password:
mysqld is alive

4. How to Check which MySQL version I am running?

# mysqladmin -u root -p version
Enter password:
mysqladmin  Ver 8.42 Distrib 5.5.28, for Linux on i686
Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Server version          5.5.28
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /var/lib/mysql/mysql.sock
Uptime:                 7 days 14 min 45 sec
Threads: 2  Questions: 36002  Slow queries: 0  Opens: 15  Flush tables: 1  Open tables: 8  Queries per second avg: 0.059

5. How to Find out current Status of MySQL server?

# mysqladmin -u root -ptmppassword status
Enter password:
Uptime: 606704  Threads: 2  Questions: 36003  Slow queries: 0  Opens: 15  Flush tables: 1  Open tables: 8  Queries per second avg: 0.059

6. How to check status of all MySQL Server Variable’s and value’s?

# mysqladmin -u root -p extended-status
Enter password:
+------------------------------------------+-------------+
| Variable_name                            | Value       |
+------------------------------------------+-------------+
| Aborted_clients                          | 3           |
| Aborted_connects                         | 3           |
| Binlog_cache_disk_use                    | 0           |
| Binlog_cache_use                         | 0           |
| Binlog_stmt_cache_disk_use               | 0           |
| Binlog_stmt_cache_use                    | 0           |
| Bytes_received                           | 6400357     |
| Bytes_sent                               | 2610105     |
| Com_admin_commands                       | 3           |
| Com_assign_to_keycache                   | 0           |
| Com_alter_db                             | 0           |
| Com_alter_db_upgrade                     | 0           |
| Com_alter_event                          | 0           |
| Com_alter_function                       | 0           |
| Com_alter_procedure                      | 0           |
| Com_alter_server                         | 0           |
| Com_alter_table                          | 0           |
| Com_alter_tablespace                     | 0           |
+------------------------------------------+-------------+

7. How to see all MySQL server Variables and Values?

# mysqladmin  -u root -p variables
Enter password:
+---------------------------------------------------+----------------------------------------------+
| Variable_name                                     | Value                                        |
+---------------------------------------------------+----------------------------------------------+
| auto_increment_increment                          | 1                                            |
| auto_increment_offset                             | 1                                            |
| autocommit                                        | ON                                           |
| automatic_sp_privileges                           | ON                                           |
| back_log                                          | 50                                           |
| basedir                                           | /usr                                         |
| big_tables                                        | OFF                                          |
| binlog_cache_size                                 | 32768                                        |
| binlog_direct_non_transactional_updates           | OFF                                          |
| binlog_format                                     | STATEMENT                                    |
| binlog_stmt_cache_size                            | 32768                                        |
| bulk_insert_buffer_size                           | 8388608                                      |
| character_set_client                              | latin1                                       |
| character_set_connection                          | latin1                                       |
| character_set_database                            | latin1                                       |
| character_set_filesystem                          | binary                                       |
| character_set_results                             | latin1                                       |
| character_set_server                              | latin1                                       |
| character_set_system                              | utf8                                         |
| character_sets_dir                                | /usr/share/mysql/charsets/                   |
| collation_connection                              | latin1_swedish_ci                            |
+---------------------------------------------------+----------------------------------------------+

8. How to check all the running Process of MySQL server?

# mysqladmin -u root -p processlist
Enter password:
+-------+---------+-----------------+---------+---------+------+-------+------------------+
| Id    | User    | Host            | db      | Command | Time | State | Info             |
+-------+---------+-----------------+---------+---------+------+-------+------------------+
| 18001 | rsyslog | localhost:38307 | rsyslog | Sleep   | 5590 |       |                  |
| 18020 | root    | localhost       |         | Query   | 0    |       | show processlist |
+-------+---------+-----------------+---------+---------+------+-------+------------------+

9. How to create a Database in MySQL server?

# mysqladmin -u root -p create databasename
Enter password:

# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 18027
Server version: 5.5.28 MySQL Community Server (GPL) by Remi
Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| databasename       |
| mysql              |
| test               |
+--------------------+
8 rows in set (0.01 sec)
mysql>

10. How to drop a Database in MySQL server?

# mysqladmin -u root -p drop databasename
Enter password:
Dropping the database is potentially a very bad thing to do.
Any data stored in the database will be destroyed.
Do you really want to drop the 'databasename' database [y/N] y
Database "databasename" dropped

11. How to reload/refresh MySQL Privileges?

# mysqladmin -u root -p reload;
# mysqladmin -u root -p refresh

12. How to shutdown MySQL server Safely?

mysqladmin -u root -p shutdown
Enter password:

# /etc/init.d/mysqld stop
# /etc/init.d/mysqld start

13. Some useful MySQL Flush commands

Following are some useful flush commands with their description.

1. flush-hosts: Flush all host information from host cache.
2. flush-tables: Flush all tables.
3. flush-threads: Flush all threads cache.
4. flush-logs: Flush all information logs.
5. flush-privileges: Reload the grant tables (same as reload).
6. flush-status: Clear status variables.

# mysqladmin -u root -p flush-hosts
# mysqladmin -u root -p flush-tables
# mysqladmin -u root -p flush-threads
# mysqladmin -u root -p flush-logs
# mysqladmin -u root -p flush-privileges
# mysqladmin -u root -p flush-status

14. How to kill Sleeping MySQL Client Process?

Use the following command to identify sleeping MySQL client process.

# mysqladmin -u root -p processlist
Enter password:
+----+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host      | db | Command | Time | State | Info             |
+----+------+-----------+----+---------+------+-------+------------------+
| 5  | root | localhost |    | Sleep   | 14   |       |					 |
| 8  | root | localhost |    | Query   | 0    |       | show processlist |
+----+------+-----------+----+---------+------+-------+------------------+

Now, run the following command with kill and process ID as shown below.

# mysqladmin -u root -p kill 5
Enter password:
+----+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host      | db | Command | Time | State | Info             |
+----+------+-----------+----+---------+------+-------+------------------+
| 12 | root | localhost |    | Query   | 0    |       | show processlist |
+----+------+-----------+----+---------+------+-------+------------------+

If you like to kill multiple process, then pass the process ID‘s with comma separated as shown below.

# mysqladmin -u root -p kill 5,10

15. How to run multiple mysqladmin commands together?

If you would like to execute multiple ‘mysqladmin‘ commands together, then the command would be like this.

# mysqladmin  -u root -p processlist status version
Enter password:
+----+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host      | db | Command | Time | State | Info             |
+----+------+-----------+----+---------+------+-------+------------------+
| 8  | root | localhost |    | Query   | 0    |       | show processlist |
+----+------+-----------+----+---------+------+-------+------------------+
Uptime: 3801  Threads: 1  Questions: 15  Slow queries: 0  Opens: 15  Flush tables: 1  Open tables: 8  Queries per second avg: 0.003
mysqladmin  Ver 8.42 Distrib 5.5.28, for Linux on i686
Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Server version          5.5.28
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /var/lib/mysql/mysql.sock
Uptime:                 1 hour 3 min 21 sec
Threads: 1  Questions: 15  Slow queries: 0  Opens: 15  Flush tables: 1  Open tables: 8  Queries per second avg: 0.003

16. How to Connect remote mysql server

To connect remote MySQL server, use the -h (host)  with IP Address of remote machine.

# mysqladmin  -h 172.16.25.126 -u root -p

17. How to execute command on remote MySQL server

Let’s say you would like to see the status of remote MySQL server, then the command would be.

# mysqladmin  -h 172.16.25.126 -u root -p status

18. How to start/stop MySQL replication on a slave server?

To start/stop MySQL replication on salve server, use the following commands.

# mysqladmin  -u root -p start-slave
# mysqladmin  -u root -p stop-slave

19. How to store MySQL server Debug Information to logs?

It tells the server to write debug information about locks in use, used memory and query usage to the MySQL log file including information about event scheduler.

# mysqladmin  -u root -p debug
Enter password:

20. How to view mysqladmin options and usage

To find out more options and usage of myslqadmin command use the help command as shown below. It will display a list of available options.

# mysqladmin --help

本地：Shell: 无密码读写Mysql数据库, 无密码被分导出Mysql数据库, mysqldump , Connect to MySQL Without Root Password on Terminal