记录 ubuntu18.04 安装并配置 MySQL
安装
$ sudo apt install mysql-server
配置
使用命令出现如下错误,原因及解决方法
$ mysql -ui root -p
ERROR 1698 (28000): Access denied for user 'root'@'localhost'
$ sudo mysql -u root # I had to use "sudo" since is new installation
mysql> USE mysql;
mysql> SELECT User, Host, plugin FROM mysql.user;
+------------------+-----------+-----------------------+
| User | Host | plugin |
+------------------+-----------+-----------------------+
| root | localhost | auth_socket |
| mysql.session | localhost | mysql_native_password |
| mysql.sys | localhost | mysql_native_password |
| debian-sys-maint | localhost | mysql_native_password |
+------------------+-----------+-----------------------+
4 rows in set (0.00 sec)
mysql> CREATE USER 'sql-test'@'localhost' IDENTIFIED BY '';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT ALL PRIVILEGES ON *.* TO 'sql-test'@'localhost';
ERROR 1133 (42000): Can't find any matching row in the user table
mysql> GRANT ALL PRIVILEGES ON *.* TO 'sql-test'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> UPDATE user SET plugin='auth_socket' WHERE User='sql-test';
mysql> FLUSH PRIVILEGES;
mysql> SELECT User, Host, plugin FROM mysql.user;
+------------------+-----------+-----------------------+
| User | Host | plugin |
+------------------+-----------+-----------------------+
| root | localhost | auth_socket |
| mysql.session | localhost | mysql_native_password |
| mysql.sys | localhost | mysql_native_password |
| debian-sys-maint | localhost | mysql_native_password |
| sql-test | localhost | auth_socket |
+------------------+-----------+-----------------------+
5 rows in set (0.00 sec)
mysql> UPDATE user SET plugin='mysql_native_password' WHERE User='sql-test';
mysql> FLUSH PRIVILEGES;
mysql> exit;
$ sudo service mysql restart
$ mysql -u sql-test
mysql>
其他命令:
- 检查MySQL服务器占用端口
netstat -nlt|grep 3306
- 检查MySQL服务器系统进程
ps -aux|grep mysql
- 查看数据库的字符集编码
show variables like '%char%';
- 重启
service mysqld restart
删除用户
存在 root@localhost
和 root@127.0.0.1
mysql> drop user root@127.0.0.1;
修复 root 登录权限
mysql -u root -p
登录失败,必须增加 sudo
才可以正常登录,修复如下
$ sudo service mysql stop
$ sudo mkdir -p /var/run/mysqld
$ sudo chown mysql /var/run/mysqld
$ sudo mysqld_safe --skip-grant-tables &
$ sudo mysqld_safe --skip-grant-tables --skip-syslog --skip-networking
$ mysql -u root
mysql> UPDATE mysql.user SET authentication_string=PASSWORD('123456') WHERE User='root';
mysql> FLUSH PRIVILEGES;
mysql> quit;
$ sudo service mysql start
导入数据
$ mysql -u root -p
mysql> create database test;
mysql> use test;
mysql> set names utf8;
mysql> source /path/to/test-tables.sql;
或者
$ mysql -u root -p test < /path/to/test-tables.sql
pymysql简单使用
- 创建数据库
pymysql.connect(host='127.0.0.1', user='test', passwd='', use_unicode=True) cursor = self.connect.cursor() cursor.execute("create database if not exists sql-test")
- 选择数据库
self.cursor.execute("use sql-test;")
- 创建表
sql = """create table if not exists saes( pos varchar(20), area varchar(20), name varchar(100), time varchar(20));""" cursor.execute(sql)
- 更新数据
cursor.execute("""select * from saes where name = %s""", item["name"]) ret = cursor.fetchone() if ret: cursor.execute( """update saes set pos = %s,area = %s, name = %s,time = %s where name = %s""", (item['pos'], item['area'], item['name'], item['time'], item['name'])) else: cursor.execute( """insert into saes(pos,area,cku,name,time) value (%s,%s,%s,%s)""", (item['pos'], item['area'], item['name'], item['time'])) connect.commit()
- 删除表
drop table tablename;
创建表
mysql> CREATE TABLE students (
-> id bigint(20) NOT NULL AUTO_INCREMENT,
-> name varchar(100) NOT NULL,
-> gender varchar(1) NOT NULL,
-> score int(11) NOT NULL,
-> PRIMARY KEY (id)
-> ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
指定字符集为 utf8
其他命令
create database new_dbname;--新建数据库
drop database old_dbnane; --删除数据库
show databases;--显示数据库
use databasename;--使用数据库
select database();--查看已选择的数据库
show tables;--显示当前库的所有表
create table tablename(fieldname1 fieldtype1,fieldname2 fieldtype2,..)[ENGINE=engine_name];--创建表
drop table tablename; --删除表
create table tablename select statement;--通过子查询创建表
desc tablename;--查看表结构
show create table tablename;--查看建表语句
alter table tablename add new_fielname new_fieldtype;--新增列
alter table tablename add new_fielname new_fieldtype after 列名1;--在列名1后新增列
alter table tablename modify fieldname new_fieldtype;--修改列
alter table tablename drop fieldname;--删除列
alter table tablename_old rename tablename_new;--表重命名
insert into tablename(fieldname1,fieldname2,fieldnamen) valuse(value1,value2,valuen);--增
delete from tablename [where fieldname=value];--删
update tablename set fieldname1=new_value where filename2=value;--改
select * from tablename [where filename=value];--查
truncate table tablename;--清空表中所有数据,DDL语句
show engines;--查看mysql现在已提供的存储引擎:
show variables like '%storage_engine%';--查看mysql当前默认的存储引擎
show create table tablename;--查看某张表用的存储引擎(结果的"ENGINE="部分)
alter table tablename ENGINE=InnoDB--修改引擎
create table tablename(fieldname1 fieldtype1,fieldname2 fieldtype2,..) ENGINE=engine_name;--创建表时设置存储引擎