Mysql介绍和实践总结

2017-09-13 12:23:50来源:cnblogs.com作者:WillkYang人点击

分享

本文首先介绍mysql的安装和基本使用、进阶操作、讲解mysql的导入导出和自动备份,然后介绍安全模式修改密码和mysql的全文本搜索功能,最后记录了个人使用mysql中遇到的问题集。

开始安装:

sudo apt-get install mysql-common mysql-server

简单使用

建库

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 = '数据库名';

进一步操作

创建用户:

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;

导入导出

导出数据和表结构:

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

数据库自动备份

新建备份脚本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(此处填写脚本绝对地址)

开启日志记录

[mysqld]server-id = 1log_bin = /var/log/mysql/mysql-bin.logmax_binlog_size = 1000Mbinlog-format = row

安全模式操作

进入安全模式修改密码

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 stopsudo /etc/init.d/mysql start

如果不是authentication_string,则可用以下方法。

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

新操作

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 ;

可能出现的问题集:

  • 描述
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/mysqldsudo chown -R mysql:mysql /var/run/mysqld
  • 描述
$ sudo /etc/init.d/mysql starties: No such file or directoryjob-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 directoryJob 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 startshell-init: error retrieving current directory: getcwd: cannot access parent directories: No such file or directoryjob-working-directory: error retrieving current directory: getcwd: cannot access parent directories: No such file or directoryjob-working-directory: error retrieving current directory: getcwd: cannot access parent directories: No such file or directoryjob-working-directory: error retrieving current directory: getcwd: cannot access parent directories: No such file or directoryjob-working-directory: error retrieving current directory: getcwd: cannot access parent directories: No such file or directoryjob-working-directory: error retrieving current directory: getcwd: cannot access parent directories: No such file or directoryjob-working-directory: error retrieving current directory: getcwd: cannot access parent directories: No such file or directoryjob-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 directoryJob 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.log2017-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/locksudo rm /var/lib/dpkg/lock

最新文章

123

最新摄影

微信扫一扫

第七城市微信公众平台