MYSQL Tips

来自Jack's Lab
跳转到: 导航, 搜索

目录

1 Notes

1.1 Change datadir

$ service mysql stop

Edit the file /etc/mysql/mysql.conf.d/mysqld.cnf and edit the datadir field to point to new location.

For example datadir         = /data/mysql

Then copy the content of /var/lib/mysql to new location:

$ cp -pr /var/lib/mysql /data

Then edit the /etc/apparmor.d/usr.sbin.mysqld file. Remove /var/lib/mysql and add the new datadir location. In my case it is
 
# Allow data dir access
 /data/mysql/ r,
 /data/mysql/** rwk,

$ systemctl restart apparmor
$ service mysql start


1.2 Change root password

$ sudo mysql
mysql > alter user 'root'@'localhost' identified with mysql_native_password by 'your_new_passwd';

https://www.configserverfirewall.com/ubuntu-linux/reset-mysql-root-password-ubuntu/


2 初始化

mysql-server-5.1 deb 包安装时,会提示设置 mysql 的 root 密码

$ mysql -u root -p
Enter password:
mysql> show databases;
 
mysql> create database YOUR_DB_NAME;
Query OK, 1 row affected (0.00 sec)
 
mysql> grant all privileges on YOUR_DB_NAME.* to USER_NAME@localhost identified by ‘YOUR_PASSWORD';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

3 Add User

mysql> CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
mysql> GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost';
mysql> FLUSH PRIVILEGES;

https://www.digitalocean.com/community/tutorials/how-to-create-a-new-user-and-grant-permissions-in-mysql

# create a MySQL user allowed access from any host.
mysql> CREATE USER 'username'@'%' IDENTIFIED BY 'password';

mysql> CREATE USER 'username'@'192.168.1.100' IDENTIFIED BY 'password';


4 Remote Access


5 连接服务器

$ mysql -h localhost -u USER_NAME -p
Enter password: 
mysql> 


6 查看数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| YOUR_DB_NAME       |
| mysql              |
+--------------------+



7 Create Database

mysql> create database if not exists YOUR_DB_NAME default charset utf8 collate utf8_general_ci;
Query OK, 1 row affected (0.03 sec)



8 Remove Database

mysql> drop database YOUR_DB_NAME;



9 查看和改变当前默认使用的数据库

mysql> select database();
+------------+
| database() |
+------------+
| NULL       |
+------------+

mysql> use YOUR_DB_NAME;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select database();
+------------+
| database() |
+------------+
|YOUR_DB_NAME|
+------------+

delete database:
mysql> drop database YOUR_DB_NAME;


10 查看数据库里的表

mysql> show tables from YOUR_DB_NAME;  (show tables;)
+-----------------------+
| Tables_in_YOUR_DB_NAME|
+-----------------------+
| wp_commentmeta        |
| wp_comments           |
| wp_links              |
| wp_options            |
| wp_postmeta           |
| wp_posts              |
| wp_term_relationships |
| wp_term_taxonomy      |
| wp_terms              |
| wp_usermeta           |
| wp_users              |
+-----------------------+



11 查看数据表属性

mysql> show table status from YOUR_DB_NAME like 'TABLE_NAME';
+------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------+
| Name       | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time          | Collation       | Checksum | Create_options | Comment |
+------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------+
| datapoints | MyISAM |      10 | Dynamic    |  136 |             54 |        7404 | 281474976710655 |        28672 |         0 |            167 | 2015-01-09 00:36:01 | 2015-01-15 12:42:26 | 2015-01-09 00:36:01 | utf8_general_ci |     NULL |                |         |
+------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------+
1 row in set (0.00 sec)



12 修改表属性

mysql> alter table YOUR_DB_NAME.TABLE_NAME auto_increment=168;
Query OK, 136 rows affected (0.05 sec)
Records: 136  Duplicates: 0  Warnings: 0

mysql> show table status from YOUR_DB_NAME like 'TABLE_NAME';
+------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------+
| Name       | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time          | Collation       | Checksum | Create_options | Comment |
+------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------+
| datapoints | MyISAM |      10 | Dynamic    |  136 |             54 |        7404 | 281474976710655 |        28672 |         0 |            168 | 2015-01-15 13:24:44 | 2015-01-15 13:24:44 | 2015-01-15 13:24:44 | utf8_general_ci |     NULL |                |         |
+------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------+
1 row in set (0.00 sec)



13 增、减表字段

 alter table YOUR_DB_NAME.TABLE_NAME add column NEW_COL int(10) DEFAULT NULL; //增加一个字段,默认为空
mysql> desc action;
+-----------+------------------+------+-----+-------------------+----------------+
| Field     | Type             | Null | Key | Default           | Extra          |
+-----------+------------------+------+-----+-------------------+----------------+
| id        | int(10) unsigned | NO   | PRI | NULL              | auto_increment |
| timestamp | timestamp        | NO   |     | CURRENT_TIMESTAMP |                |
| action    | text             | NO   |     | NULL              |                |
| processed | int(8) unsigned  | NO   |     | 0                 |                |
+-----------+------------------+------+-----+-------------------+----------------+
4 rows in set (0.00 sec)

mysql> alter table cos.action add column `nid` int(10) unsigned default null;
Query OK, 20 rows affected (0.03 sec)
Records: 20  Duplicates: 0  Warnings: 0

mysql> desc action;
+-----------+------------------+------+-----+-------------------+----------------+
| Field     | Type             | Null | Key | Default           | Extra          |
+-----------+------------------+------+-----+-------------------+----------------+
| id        | int(10) unsigned | NO   | PRI | NULL              | auto_increment |
| timestamp | timestamp        | NO   |     | CURRENT_TIMESTAMP |                |
| action    | text             | NO   |     | NULL              |                |
| processed | int(8) unsigned  | NO   |     | 0                 |                |
| nid       | int(10) unsigned | YES  |     | NULL              |                |
+-----------+------------------+------+-----+-------------------+----------------+
5 rows in set (0.01 sec)

mysql> alter table cos.action drop column nid;
Query OK, 20 rows affected (0.02 sec)
Records: 20  Duplicates: 0  Warnings: 0

mysql> desc action;
+-----------+------------------+------+-----+-------------------+----------------+
| Field     | Type             | Null | Key | Default           | Extra          |
+-----------+------------------+------+-----+-------------------+----------------+
| id        | int(10) unsigned | NO   | PRI | NULL              | auto_increment |
| timestamp | timestamp        | NO   |     | CURRENT_TIMESTAMP |                |
| action    | text             | NO   |     | NULL              |                |
| processed | int(8) unsigned  | NO   |     | 0                 |                |
+-----------+------------------+------+-----+-------------------+----------------+
4 rows in set (0.00 sec)




14 修改表字段

 alter table YOUR_DB_NAME.TABLE_NAME modify COL_NAME int(10) default null;
 alter table YOUR_DB_NAME.TABLE_NAME change OLD_COL_NAME NEW_COL_NAME VARCHAR(10) default null;
mysql> alter table cos.action modify nid int(10) default null;
Query OK, 20 rows affected (0.02 sec)
Records: 20  Duplicates: 0  Warnings: 0

mysql> desc cos.action;
+-----------+------------------+------+-----+-------------------+----------------+
| Field     | Type             | Null | Key | Default           | Extra          |
+-----------+------------------+------+-----+-------------------+----------------+
| id        | int(10) unsigned | NO   | PRI | NULL              | auto_increment |
| timestamp | timestamp        | NO   |     | CURRENT_TIMESTAMP |                |
| action    | text             | NO   |     | NULL              |                |
| processed | int(8) unsigned  | NO   |     | 0                 |                |
| nid       | int(10)          | YES  |     | NULL              |                |
+-----------+------------------+------+-----+-------------------+----------------+
5 rows in set (0.00 sec)




15 数据库备份

 $ mysqldump -u user_name -p password --opt YOUR_DB_NAME | gzip -9 > /root/YOUR_DB_NAME-db-20110801.sql.gz


#-d 不导出数据只导出结构 --add-drop-table 在每个create语句之前增加一个drop table 
mysqldump -u root -p -d --add-drop-table db_name >/root/eprs_db.sql


# 导出一个表
mysqldump -u 用户名 -p 数据库名 表名> 导出的文件名
mysqldump -u root -p db_name users> db_users.sql



16 数据库恢复

 $ mysql -uUSER_NAME -p USER_PASSWORD YOUR_DB_NAME < /root/YOUR_DB_NAME-db-20110801.sql









个人工具
名字空间

变换
操作
导航
工具箱