工作上遇到的一些问题-- 数据库相关

古城微笑少年丶 2021-12-09 07:27 421阅读 0赞

# 数据库导入导出 #

命令：  
方法1：

mysqldump -h localhost -u root -p test01 > /home/chuzj/daochu.sql
    mysql -h localhost -u root -p test01 < /home/chuzj/daochu.sql --default-character-set=utf8

–default-character-set=utf8 解决中文乱码

导入的方法2：  
在sql下先创建表 ：

use weifang
     source /home/user/data/fileName.sql

# 数据库表的导入导出 #

mysqldump -h192.168.1.1 -uroot -p123456 database tablename >E:/album710/app_download_stat.sql
    mysql -h192.168.1.1 -uroot -p album710 < E:/album710/app_download_stat.sql

注意：

### 1 报如下错： ###

Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that
    changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete
    dump, pass --all-databases --triggers --routines --events.

关于GTID是5.6以后，加入了全局事务 ID (GTID) 来强化数据库的主备一致性，故障恢复，以及容错能力。  
官方给的：A global transaction identifier (GTID) is a unique identifier created and associated with each transaction committed on the server of origin (master).所以可能是因为在一个数据库里面唯一，但是当导入其他的库就有可能重复。所有会有一个提醒。可以通过添加–set-gtid-purged=off 或者–gtid-mode=OFF这两个参数设置。很有可能是在导入库中重新生产GTID，而不用原来的。  
写成如下：

mysqldump -uroot --set-gtid-purged=off -p xqsj_db > xqsj_db20160811.sql

### 2 报如下错： ###

当你使用source导入时候

MySQL server has gone away

在我们使用mysql导入大文件sql时可能会报MySQL server has gone away错误，该问题是max\_allowed\_packet配置的默认值设置太小，只需要相应调大该项的值之后再次导入便能成功。该项的作用是限制mysql服务端接收到的包的大小，因此如果导入的文件过大则可能会超过该项设置的值从而导致导入不成功！下面我们来看一下如何查看以及设置该项的值。  
查看 max\_allowed\_packet 的值：

show global variables like 'max_allowed_packet';

+--------------------+---------+
    | Variable_name      | Value   |
    +--------------------+---------+
    | max_allowed_packet | 4194304 |
    +--------------------+---------+

可以看到默认情况下该项的大小只有4M，接下来将该值设置成150M(1024*1024*150)

set global max_allowed_packet=157286400;

然后再看看大小：

show global variables like 'max_allowed_packet';

如果任然报错，则继续再调大一些就行，请注意通过在命令行中进行设置只对当前有效，重启mysql服务之后则恢复默认值，但可以通过修改配置文件（可以在配置文件my.cnf中添加**max\_allowed\_packet=150M**即可）来达到永久有效的目的，可其实我们并不是经常有这种大量数据的导入操作，所以个人觉得通过命令行使得当前配置生效即可，没有必要修改配置文件。

### 3 报错如下： ###

使用mysqldump时报错(1064)，这个是因为mysqldump版本太低与当前数据库版本不一致导致的。

mysqldump: Couldn't execute 'SET OPTION SQL_QUOTE_SHOW_CREATE=1': You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'OPTION SQL_QUOTE_SHOW_CREATE=1' at line 1 (1064)

查看mysqldump版本

> mysqldump --version
    mysqldump Ver 10.13 Distrib 5.1.61, for redhat-linux-gnu (x86_64)

查看mysql版本

>mysql           //或者登陆mysql，select version();也可查看版本
    Server version: 5.6.25-log Source distribution

### 4 报错如下： ###

导出时指定字符集，这个是因为字符集错了。是–default-character-set=utf8

Character set 'utf-8' is not a compiled character set and is not specifie .
    --default-character-set=utf-8

# 数据库创建用户和用户授权，删除，更新 #

### 1 创建用户 ###

create user 'zhangsan'@'localhost' identified by '123456';

此处的"localhost"，是指该用户只能在本地登录，不能在另外一台服务器上远程登录。如果想远程登录的话，将"localhost"改为"%"，表示在任何一台服务器上都可以登录。也可以指定某台服务器可以远程登录。

create user 'zhangsan'@'%' identified by '123456';

### 2 查询所有用户 ###

select user,host from mysql.user;

### 3 用户分配权限 ###

授予用户test通过外网IP对数据库“testdb”的全部权限

grant all privileges on 'testdb'.* to 'test'@'%' identified by '1234';  
    -- 刷新权限
    flush privileges;

授予用户“test”通过外网IP对于该数据库“testdb”中表的创建、修改、删除权限,以及表数据的增删查改权限

grant create,alter,drop,select,insert,update,delete on testdb.* to test@'%';

注意：  
修改完权限以后 一定要刷新服务，或者重启服务，刷新服务用：flush privileges;

### 4 查看用户权限 ###

--查看用户“test”
    show grants for test;

### 5 删除用户 ###

--删除用户“test”
    drop user test@localhost ;
    --若创建的用户允许任何电脑登陆，删除用户如下
    drop user test@'%';

### 6 更改密码 ###

--方法1，密码实时更新；修改用户“test”的密码为“1122”
    set password for test =password('1122');
    --方法2，需要刷新；修改用户“test”的密码为“1234”
    update  mysql.user set  password=password('1234')  where user='test'
    --刷新
    flush privileges;

后续继续补充。。。。