查看MYSQL Tips的源代码
←
MYSQL Tips
跳转到:
导航
,
搜索
因为以下原因,你没有权限编辑本页:
您刚才请求的操作只有这个用户组中的用户才能使用:
用户
您可以查看并复制此页面的源代码:
== Notes == === Change datadir === <source lang=bash> $ 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 </source> <br> === Change root password === <source > $ sudo mysql mysql > alter user 'root'@'localhost' identified with mysql_native_password by 'your_new_passwd'; </source> https://www.configserverfirewall.com/ubuntu-linux/reset-mysql-root-password-ubuntu/ <br> == 初始化 == mysql-server-5.1 deb 包安装时,会提示设置 mysql 的 root 密码 <source lang=bash> $ 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) </source> == Add User == <source lang=bash> mysql> CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password'; mysql> GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost'; mysql> FLUSH PRIVILEGES; </source> https://www.digitalocean.com/community/tutorials/how-to-create-a-new-user-and-grant-permissions-in-mysql <source lang=bash> # 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'; </source> <br> == Remote Access == * https://www.configserverfirewall.com/ubuntu-linux/mysql-allow-remote-connections/ * https://www.configserverfirewall.com/ubuntu-linux/enable-mysql-remote-access-ubuntu/ <br> == 连接服务器 == <source lang=bash> $ mysql -h localhost -u USER_NAME -p Enter password: mysql> </source> == 查看数据库 == <source lang=bash> mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | YOUR_DB_NAME | | mysql | +--------------------+ </source> <br><br> == Create Database == <source lang=bash> mysql> create database if not exists YOUR_DB_NAME default charset utf8 collate utf8_general_ci; Query OK, 1 row affected (0.03 sec) </source> <br><br> == Remove Database == <source lang=bash> mysql> drop database YOUR_DB_NAME; </source> <br><br> == 查看和改变当前默认使用的数据库 == <source lang=bash> 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; </source> == 查看数据库里的表 == <source lang=bash> 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 | +-----------------------+ </source> <br><br> == 查看数据表属性 == <source lang=bash> 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) </source> <br><br> == 修改表属性 == <source lang=bash> 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) </source> <br><br> == 增、减表字段 == alter table YOUR_DB_NAME.TABLE_NAME add column NEW_COL int(10) DEFAULT NULL; //增加一个字段,默认为空 <source lang=bash> 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) </source> <br><br> == 修改表字段 == 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; <source lang=bash> 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) </source> <br><br> == 数据库备份 == <source lang=bash> $ mysqldump -u user_name -p password --opt YOUR_DB_NAME | gzip -9 > /root/YOUR_DB_NAME-db-20110801.sql.gz </source> <source lang=bash> #-d 不导出数据只导出结构 --add-drop-table 在每个create语句之前增加一个drop table mysqldump -u root -p -d --add-drop-table db_name >/root/eprs_db.sql </source> <source lang=bash> # 导出一个表 mysqldump -u 用户名 -p 数据库名 表名> 导出的文件名 mysqldump -u root -p db_name users> db_users.sql </source> <br><br> == 数据库恢复 == <source lang=bash> $ mysql -uUSER_NAME -p USER_PASSWORD YOUR_DB_NAME < /root/YOUR_DB_NAME-db-20110801.sql </source> <br><br> <br><br> <br><br> <br><br>
返回到
MYSQL Tips
。
个人工具
3.147.66.149
该IP地址的讨论
登录
名字空间
页面
讨论
变换
查看
阅读
查看源代码
查看历史
操作
搜索
导航
首页
社区专页
新闻动态
最近更改
随机页面
帮助
工具箱
链入页面
相关更改
特殊页面