mysql数据库学习（四）

https://www.cnblogs.com/ffzzcommsoft/p/14798777.html

https://www.cnblogs.com/ffzzcommsoft/p/14801728.html

https://www.cnblogs.com/ffzzcommsoft/p/14807605.html

https://www.cnblogs.com/ffzzcommsoft/p/14810490.html

一、MySQL客户端工具mysql

　　1.1、>mysql [options] [database]

　　　　-u, --user=name　　　　　　　　 指定用户名可不空格加用户名

　　　　-p,--password[=name] 　　     指定密码

　　　　-h　　　　　　　　　　　　 　　　 指定服务器IP或域名

　　　　-P　　　　　　　　　　　　　　　　 端口（大写）

　　　　-e,--execute=name　　　　  　　执行SQL语句并退出 语法>mysql -u -p database -e "sqlstatement"

　　1.2、mysqladmin

　　　　 mysqladmin是一个执行管理操作的客户端程序。可以用来检查服务器的配置和当前状态，创建并删除数据库等

　　　　 可以通过：mysqladmin --help 指令查看帮助文档

　　　　>mysqladmin -uroot -pxxx create 'database_name';

　　　　>mysqladmin -uroot -pxxx drop   'database_name';

　　1.3、mysqlbinlog

 

　　1.4、mysqldump

　　　　 mysqldump客户端工具用来备份数据库或在不同数据库之间进行数据迁移。备份内容包含创建表，及插入表的SQL语句。

　　　　 语法-------->连接选项--------------->输出内容选项

　　　　 >mysqldump -uroot -p123456 sqllevel test_innodb_lock >test_innnodb_lock.sql#管理员权限

　　　　 >mysqldump -uroot -p123456 -T /yourotue sqllevel city

#使用-T参数  不需要使用分号

　　　　>mysqldump -uroot -p123456 -T C:/ProgramData/MySQL  sqllevel  test_innodb_lock

　　　　记录出现的错误:

mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: Couldn't find table: "test_innodb_lock;"

mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: Got error: 1049: Unknown database 'server' when selecting the database

mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: Got error: 1290: The MySQL server is running with the --secure-file-priv option so it cannot execute this statement when executing 'SELECT INTO OUTFILE'

>show [global] variables like '%secure%';　#查看secure_file_priv参数的值需要保证没有空格，且有权限的位置，设置在mysql相关的路径下，否则会报Errcode：13-Permission denied

　　1.5、mysqlimport/source

　　　　 mysqlimport是客户端数据导入工具，用来导入mysqldump加-T参数后导出的文本文件

　　　　 语法：mysqlimport　　[options]　　db_name　　textfile1　　[textfile2...]

　　　　 如果需要导入sql文件，可以使用mysql中的source指令

　　　　 语法：source　/youfileroute/file.sql

　　1.6、mysqlshow:对象查找工具，快速查找存在哪些数据库、数据库中的表、表中的列或者索引

　　　　 语法：mysqlshow [options] [db_name[table_name[col_name]]]

　　　　 --count  显示数据库及表的统计信息（数据库、表均可以不指定）

　　　　 -i　　　　显示指定数据库或者指定表的状态信息

$mysqlshow -uroot -p123456 --count　　　　　　      #查看当前mysql当中所有数据库、表以及行数

$mysqlshow -uroot -p123456 sqllevel   --count　   #查看sqllevel数据库有哪些表、表多少列，多少行

$mysqlshow -uroot -p123456 sqllevel   city -i　    #查看sqllevel数据库city表具体信息 

二、Mysql日志

　　2.1、错误日志：存储MySQL启动/停止/运行过程中发生的任何错误时的相关信息。默认开启，

　　　　 默认存放目录linux（var/lib/mysql）,默认名称主机名hostname.err

　　　　 >show variables like 'log_error%'; #查看日志位置log_error指定标示错误日志在当前目录下名为DESKTOP-DKSCN61.err,windows

　　　　 #log_error_verbosity标示1 错误信息；2  错误信息和告警信息； 3：错误信息、告警信息和通知信息windows

　　　　 

　　2.2、二进制日志：默认不开启，在my.cnf配置文件中配置.使用mysqlbinlog工具查看二进制日志文件

　　　　 $mysqlbinlog /youfileroute/filename    　　#查看STATEMENT格式的日志

　　　　 $mysqlbinlog -vv /youfileroute/filename   #查看ROW格式的日志

　　　　 日志文件mysqlbin.000001 日志文件索引mysqlbin.index

　　　　日志格式：STATEMENT/ROW/MIXED

　　　　2.2.1、删除日志

　　　　　　方式一：>Reset Master;#删除全部binlog日志后，日至编号将从xxxx.000001重新开始

　　　　　　方式二：>purge master logs to 'mysqlbin.******'；#删除******编号之前的所有日志

　　　　　　方式三：>purge master logs to before 'yyyy-mm-dd hh24:mi:ss'；#删除指定日期之前的所有日志

　　　　　　方式四：>在my.cnf配置参数expire_logs_days=#,过期天数

　　　　　　　　--expire_logs_days=num

　　2.3、查询日志.默认不开启，记录了客户端的所有操作语句，包括查询语句.在my.cnf配置如下信息

　　　　 general_log=1　　#0表示关闭，1开启

　　　　 general_log_file=file_name　　#默认文件名host_name.log

　　2.4、慢查询日志：默认是关闭的

　　　　 慢查询日志记录了所有执行时间超过了long_query_time设置值并且扫描记录数不小于min_examined_row_limit的所有的SQL日志。

　　　　 查看慢查询日志的命令mysqldumpslow$mysqldumpslow xx.log

　　　　 long_query_time默认10s，最小为0，精度可以到微秒

　　　　 slow_query_log=1

　　　　 slow_query_log_file=slow_query.log

　　　　 long_query_time=2

 三、MySQL的复制

　　3.1、复制概述：复制是将主数据库中的DDL和DML操作通过二进制日志传到从库服务器中，然后在从库上对这些日志重新执行（也叫重做）

　　　　 从而使得从库和主库的数据保持同步

　　　　 MySQL支持一台主库同时向多台从库进行复制，从库同时也可以作为其他从服务器的主库，实现链状复制

　　3.2、复制原理

　　3.3、复制优势

　　　　 主库出现问题，可以快速切换到从库提供服务

　　　　 可以在从库上执行查询操作，从主库中更新，实现读写分离，降低主库的访问压力

　　　　 可以在从库中执行备份，以避免备份期间影响主库的服务　　

　　3.4、搭建步骤　

#master主数据库的操作步骤

#第一步：在master的配置文件my.cnf中，配置如下内容
#mysql 服务ID，保证在整个集群环境中的唯一

server-id=1
#mysql binlog日志的存储路径和文件名
log-bin=/var/lib/mysql/mysqlbin
#是否只读，1代表只读，0代表读写
read-only=0
#忽略数据库，指不需要同步的数据库
binlog-ignore-db=mysql
#可以指定同步数据库
#binlog-do-db=db01

#第二步：重启MySQL

service mysql restart;#centos6

#第三步：创建同步数据的账户，并且进行授权操作

>grant replication slave on *.* to 'loginname'@'slaveip' identified by 'password';
>fiush privileges;#刷新权限

#第四步：查看master状态

初步得到从mysqlbin.000002日志文件的154行可以开始同步
file:从哪个日志文件开始推送日志文件
position:从哪个位置开始推送日志
Binlog_Ignore_DB:指定不需要同步的数据库

#slave从数据库的操作步骤

#第一步：从数据库my.cnf文件配置

server-id=2


log-bin=/var/lib/mysql/mysqlbin

#第二步：重启配置文件配置

#第三步：执行命令

change master to master_host='masterip',master_user='loginname',master_password='pass',

master_log_file='mysqlbin.000002',master_log_pos=154;

#第四步：开启同步操作

>start slave;


>show slave status;


>stop slave;#停止同步操作

到此可以在master数据库中操作数据库增删改操作，并验证是否同步了

重新配置主从

stop slave;

reset master;

四、其他

　　分析并优化：

　　1、分页查询优化2、条件查询分析3、读写分离4、MySQL服务器优化5、应用优化