数据库的操作与踩过的坑

最近上线一个项目，采用的数据库是mysql，在部署的过程中碰到不少问提，总结出来：

1、对于线上的数据库连接，用navicat for mysql 连接线上数据库注意事项：

 1）常规里面填写的用户名和密码以及端口号是你要连接的数据库的用户名、密码和端口号；

 2）ssh里面填写的用户名和密码是你连接的服务器的用户名和密码；

2、将表assets_asset中的id插入到表assets_disk中asset_id

insert into assets_cpu (asset_id) select id from assets_asset
清空数据：truncate table name

3、数据库批量操作

（1）例1、将来自表assets_asset的id插入assets_cpu表数据asset_id：

insert into assets_cpu (asset_id) select id from assets_asset
where id not in (select asset_id from assets_cpu) 

例2、将表assets_asset字段id,1赋值到表assets_asset_tags两个字段asset_id,tag_id：

insert into assets_asset_tags (asset_id,tag_id)
select distinct id,1 from assets_asset

Tip:select distinct 有distinct这个keyword来过滤掉多余的反复记录仅仅保留一条；

（2）清空表数据

truncate table assets_server

 (3)批量更新a表的name到b表的hosted_on_id：

update assets_server a set hosted_on_id = (select name from assets_asset b where a.asset_id = b.id)

 

4、查看数据库已授权用户：

SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;

 

mysql启动服务：

systemctl start mariadb.service

mysql修改密码：

set password=password('123456');

数据库授权远程登陆：

grant all privileges on *.* to 'root'@'%' identified by '1234!@#$abc';
flush privileges;

5、采用Django框架的时候，项目需要做数据库映射，采用python3 manage.py makemigrations 发现自己定义的model没有生成时，这时候我们可以先进行一个手动的操作：

python3.6 manage.py makemigrations --empty assets ###assets是你建立的appname;

做完这一个之后，在执行python3.6 manage.py makemigrations语句时就不会弹出No changes detected

6、快速建表，并且避免错误：

CREATE TABLE 'assets_cpu' ('id' int(10) NOT NULL auto_increment,PRIMARY KEY  ('id')) ENGINE=InnoDB DEFAULT CHARSET=utf8;

7、如果报错某个表exist,则运行：

python3 manage.py showmigrations

显示出所有的app及对应的已经生效的migration文件

python3.6 manage.py migrate –fake pay zero

truncate table  name

8、数据库结构操作：

（1）函数操作：

###数据库新建多条自增id值，id的自增区间为4到255：
BEGIN 
DECLARE i INT DEFAULT 258;
WHILE i<255 and i>4 
DO 
insert into assets_ipaddress SET id = i+1; 
SET i=i+1; 
END WHILE ; 
commit; 
END

 9、查看指定数据库所占内存空间大小：

use information_schema--此命令是使用数据库是MySQL自带的信息数据库，保存着关于MySQL服务器所有其他数据库的信息，类似于数据字典，如数据库名，表名，表的访问权限，列名，索引，约束等
select concat(round(sum(DATA_LENGTH)/1024/1024+sum(INDEX_LENGTH)/1024/1024),'M') 
from information_schema.tables where table_schema=‘fupu_daas_cmdb’;

10、新建django项目的时候出现报错：'Did you install mysqlclient or MySQL-python'

 解决办法：pip install mysql-python;

在app的__init__.py中加入：

import pymysql

pymysql.install_as_MySQLdb()

11、数据库操作：

#增加字段：

insert into assets_ipaddress(alibe_ip) values ('10.10.66.11')

#删除字段：

delete from assets_ipaddress where dead_ip='10.10.66.11'

#删除某列字段：alter table assets_ipaddress drop column memo;

#当表assets_asset中的name字段不存在于表assets_ip_tags时，将来自表assets_asset的id和name插入到表assets_ip_tags中的asset_id,ip两个字段：

insert into assets_ip_tags (asset_id,ip) select id,name from assets_asset where name not in (select ip from assets_ip_tags)

#更新表assets_server的hosted_on_id字段，此字段来源于表assets_asset的name字段：

update assets_server a set hosted_on_id = (select name from assets_asset b where a.asset_id=b.id)

#当出现IP资源池点击不正确的时候，用它：

update assets_ip_tags a set asset_id=(select id from assets_asset b where a.ip=b.name)
delete from assets_ip_tags where ip not in (select name from assets_asset)

12、当数据库远程连接出现报错Access denied for user 'root'@'localhost' (using password:yes)时：

解决办法：在/etc/my.cnf的[mysqld]下加一句话：skip-grant-tables;

13、python Django开发的时候，需要进行数据库接口连接与操作的问题：

突然出现某个数据库相关的操作，报错(2006, "MySQL server has gone away (BrokenPipeError(32, 'Broken pipe'))")
原因：即某个mysql长连接很久没有新的请求发起，达到了server端的timeout，被server强行关闭。
此后再通过这个connection发起查询的时候，就会报错server has gone away
解决办法：/etc/my.cnf下添加：
wait_timeout=2880000
interactive_timeout = 2880000