Mysql介绍和实行总计

图片 1

3)如若无需复制,恐怕是从库,关闭binlog,

本文重要介绍mysql的装置和大旨接受、数据库导入导出和电动备份,然后介绍安全格局改正密码和mysql的全文本寻找效果,最终记录了个人使用mysql中相遇的难题集。

能上学到如何东西:

 

ERROR 1419 : You do not have the SUPER Privilege and Binary Logging is Enabled

导入导出

导出数据和表构造:

mysqldump -uroot -p abc(数据库名) > abc.sql
敲回车后输入密码

只导出表构造

mysqldump -uroot -p -d abc > abc.sql

导入数据库
1、首先建空数据库

mysql> create database abc;

2、导入数据库

mysql -u root -p abc(数据库名) < abc.sql

图片 2

2)让具备顾客具有试行相像functions的权杖,危殆,不引入,

一发操作

创制客户:

create user xxx identified by ‘password’;

重命名:

rename user aaa to bbb;

除去客商:

drop user aaa;

呈现权限:

show grants for aaa(用户);

授予权限:

grant select on xxx(数据库).* to aaa(用户);

加之有些数据库的所有事权力:

grant all on  xxx(数据库).* to aaa(用户);
grant all on  xxx(数据库).* to aaa(用户)@localhost;

裁撤授权:

revoke all on *.* from aaa(用户)@localhost;

更正权限

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%’   WITH GRANT OPTION;

如上操作实现今后记得刷新权限:

flush privileges;

zxing管理条码的辨别和调换,蕴含带logo的二维码

 

原因:function / trigger 中有dangerous
statements校勘数据库,错误只在启用binary
logging选项进行主从复制的服务器上现身。

简短利用

建库

CREATE DATABASE IF NOT EXISTS yourdbname DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

建表

create table MyClass(id int(4) not null primary key auto_increment,name char(20) not null,sex int(4) not null default '0',degree double(16,2));

insert into MyClass values(1,'Tom',96.45),(2,'Joan',82.99), (2,'Wang', 96.59);

delete from MyClass where id=1;

update MyClass set name='Mary' where id=1;

select * from MyClass;

来得全数的view

select * from information_schema.TABLES where table_type='view' AND table_schema = '数据库名';

安装配置:

  管理办法:只怕又其它进度或地方在利用该数据库,重启数据库服务消除

1)导入数据的客商不仅仅须求CREATE ROUTINE, ALTELAND ROUTINE, CREATE T奥迪Q5IGGEMurano,
ALTE中华V T凯雷德IGGE大切诺基, CREATE FUNCTION 和 ALTE翼虎 FUNCTION 权限,还索要SUPE昂科拉privileges 权限,使用一流客商导入数据。

新操作

Mysql全文本寻找

Mysql5.6从今未来扶持InnoDB,中文的全文本搜索,内置使用n-gram为分词微型机,还帮忙中文~。

创办索引

create fulltext index ngram_idx on tag(Title) with parser ngram;
或
alter table tag add fulltext index ngram_idx(Title) with parser ngram;

获取援助的最小分词长度

SHOW VARIABLES LIKE 'ft_min_word_len';
//unix系统可在/etc/my.cnf中修改
[mysqld]
ft_min_word_len = 1

始发使用

select Title,match(Title) against('清水') from tag ;

根据bcprov的密码加密和存款和储蓄

图片 3

正文实例呈报了MYSQL设置触发器权限的方式,针对权限错误的状态特别实用。具体剖析如下:

数据库自动备份

新建备份脚本xxx.sh,输入以下内容

#!/bin/bash

# 要备份的数据库名,多个数据库用空格分开
databases=("db1", "db2") 

# 备份文件要保存的目录,注意当前用户必须用户保存目录的读写权限
basepath='/root/backup/mysql/'

if [ ! -d "$basepath" ]; then
  mkdir -p "$basepath"
fi

# 循环databases数组
for db in ${databases[*]}
  do
    # 备份数据库生成SQL文件
    nice -n 19 /usr/bin/mysqldump -uroot -pcd32d5e86e --database $db > $basepath$db-$(date +%Y%m%d).sql

    # 将生成的SQL文件压缩
    nice -n 19 tar zPcf $basepath$db-$(date +%Y%m%d).sql.tar.gz -C $basepath $db-$(date +%Y%m%d).sql

    # 删除7天之前的备份数据
    find $basepath -mtime +7 -name "*.sql.tar.gz" -exec rm -rf {} \;
  done

  # 删除生成的SQL文件
  rm -rf $basepath/*.sql

运用crontab设置定期职分,在极限输入crontab
-e,参加以下内容,此职务为天天3点自动实行。

0 3 * * * bash xxx.sh(此处填写脚本绝对地址)

A.权限设计流程:两条线
一是客户–》分组–》剧中人物–》功效
二是顾客–》岗位–》作用

相当2 : 无法用排他锁锁定该数据库,以实施该操作。 (.Net SqlClient Data
Provider卡塔尔(英语:State of Qatar)

mysql导入数据提醒没有SUPE昂科威 Privilege权限管理,如下所示:

张家界方式操作

跻身安全方式校勘密码

mysqld_safe --skip-grant-tables &

select user,host,password from user where user="root"

今是昨非版本的mysql改进客户密码格局不一致等,供给查阅mysql->user中的密码字段,假若不是password的话正是authentication_string。

authentication_string的改造议程不太相仿:

use mysql;
update user set authentication_string=PASSWORD("") where User='root';
update user set plugin="mysql_native_password";
flush privileges;
quit;
sudo /etc/init.d/mysql stop
sudo /etc/init.d/mysql start

万一不是authentication_string,则可用以下措施。

update user set password=PASSWORD("your_password") where user="root" and host=“localhost"

2.
具备的U帕杰罗L访问都进展了详尽的日记记录,满含action耗费时间、视图耗时、总耗费时间,不管是访问成功或然战败,大概有没权力,在日志表都详细笔录

依据提醒向导一步步走就行了

You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)”.

图片 4

效果与利益介绍:

 

导入function 、 trigger 到 MySQL database,报错:

伊始安装:

sudo apt-get install mysql-common mysql-server

1.
系统达成了遵照ULANDL的权力管理,页面操作调整到按键品级,依据权限配置举办展示,并在拦截器再一次验证,唯有分配权限后本事扩充操作

有的导入战败以致管理方案

化解方法如下:

大概现身的难题集:

  • 描述

2017-05-04T01:21:32.004560Z mysqld_safe Logging to '/var/log/mysql/error.log'.
2017-05-04T01:21:32.023009Z mysqld_safe A mysqld process already exists

淹不能:

$ sudo killall mysqld
  • 描述

2017-05-04T01:22:26.486677Z mysqld_safe Logging to '/var/log/mysql/error.log'.
2017-05-04T01:22:26.488204Z mysqld_safe Directory '/var/run/mysqld' for UNIX socket file don't exists.

解决措施:

sudo mkdir -p /var/run/mysqld
sudo chown -R mysql:mysql /var/run/mysqld
  • 描述

$ sudo /etc/init.d/mysql start
ies: No such file or directory
job-working-directory: error retrieving current directory: getcwd: cannot access parent directories: No such file or directory
[....] Starting mysql (via systemctl): mysql.servicejob-working-directory: error retrieving current directory: getcwd: cannot access parent directories: No such file or directory
Job for mysql.service failed because the control process exited with error code. See "systemctl status mysql.service" and "journalctl -xe" for details.

解决方法:

当前文件夹不是实际目录导致
cd到一个实际目录位置即可
  • 描述

sudo /etc/init.d/mysql start
shell-init: error retrieving current directory: getcwd: cannot access parent directories: No such file or directory
job-working-directory: error retrieving current directory: getcwd: cannot access parent directories: No such file or directory
job-working-directory: error retrieving current directory: getcwd: cannot access parent directories: No such file or directory
job-working-directory: error retrieving current directory: getcwd: cannot access parent directories: No such file or directory
job-working-directory: error retrieving current directory: getcwd: cannot access parent directories: No such file or directory
job-working-directory: error retrieving current directory: getcwd: cannot access parent directories: No such file or directory
job-working-directory: error retrieving current directory: getcwd: cannot access parent directories: No such file or directory
job-working-directory: error retrieving current directory: getcwd: cannot access parent directories: No such file or directory
[....] Starting mysql (via systemctl): mysql.servicejob-working-directory: error retrieving current directory: getcwd: cannot access parent directories: No such file or directory
Job for mysql.service failed because the control process exited with error code. See "systemctl status mysql.service" and "journalctl -xe" for details.

按照提示:See "systemctl status mysql.service" and "journalctl -xe" for details.
但是并么有什么卵用,直接看mysql的log:/var/log/mysql/error.log
2017-05-04T01:37:56.583745Z 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.

化解措施:

杀掉所有mysqld进程:killall mysqld 
再次sudo /etc/init.d/mysql start 成功
  • 描述

dpkg被锁定

解决方法

sudo rm /var/cache/apt/archives/lock
sudo rm /var/lib/dpkg/lock

怎么谐和完结分页、动态条件的询问

相关文章