mysql实践总结
⾸先介绍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;
显⽰所有视图
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(⽤户);mysql下载starting the server
授予某个数据库的全部权限
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). -C $basepath $db-$(date +%Y%m%d).sql
# 删除7天之前的备份数据
find $basepath -mtime +7 -name "*." -exec rm -rf {} \;
done
# 删除⽣成的SQL⽂件
rm -rf $basepath/*.sql
使⽤crontab设置定时任务，在终端输⼊crontab -e，加⼊以下内容，此任务为每天3点⾃动执⾏。
03 * * * bash xxx.sh(此处填写脚本绝对地址)
开启⽇志记录
[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
max_binlog_size = 1000M
binlog-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 stop
sudo /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/myf中修改
[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/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

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。