MYSQL汇总
一.1.1 MYSQL
一.1.1.1 基础特性
1）性能卓越，服务稳定，很少出现异常宕机；
2）开放源代码且无版权制约，自主性强，使用成本低；
3）历史悠久，社区及用户非常活跃，遇到问题，可快速获得帮助；
4）软件体积小，安装使用简单，易于维护，安装及维护成本低；
5）支持多种操作系统，提供多种API接口，支持多种开发语言，对PHP语言语言无缝支持；
6）品牌口碑效应。
一.1.1.2 基础说明
注：数据库重新初始化要切记先删除data对应目录的数据。
企业环境创建数据库：
a.根据开发的程序确定字符集（建议UTF8）；
b.编译时指定字符集，例如：
-DDEFAULT_CHATSET=utf8 \
-DDEFAULT_CHLLATION=utf8_general_ci \
c.当需要的字符集不同于默认字符集时，指定字符集创建数据库即可。
企业环境数据库授权状态：
a.博客、CMS等产品的数据库授权：
对于web连接用户尽量采用最小化原则，较多开源软件都是web界面安装，需要SELECT、INSERT、UPDATE、DELETE和CREATE、DROP（较危险）权限；
> grant select,insert,update,delete,create,drop on blog.* to ‘blog’@’10.0.0.%’ identified by ‘000000’;
b.生成数据库表后，要收回CREATE、DROP权限；
> REVOKE CREATE on blog.* FROM ‘blog’@’10.0.0.%’;
> REVOKE DROP on blog.* FROM ‘blog’@’10.0.0.%’;
c.生产环境针对主库（写为主读为辅）用户的授权；
主库
> GRANT SELECT,INSERT,UPDATE,DELETE ON ‘blog’.* TO ‘blog’@’10.0.0.%’ identified by ‘000000’;
从库
> GRANT SELECT ON ‘blog’.* TO ‘blog’@’10.0.0.%’ identified by ‘000000’;
mysql记录UUID的文件
[root@M-mysql /r2/mysqldata]# cat auto.cnf
[auto]
server-uuid=fda28692-9e31-11e7-bf34-000c2907c998
数据库操作记录文件
/root/.mysql_history
mysql参数说明
-V --version 查看版本；-e 实现非交换式对话；-U 当发出没有WHERE或LIMIT关键字的UPDATE或DELETE时，mysql程序将拒绝执行；
防止数据库误操作
# echo “alias mysql=’mysql -U’” >>/etc/profile
# source /etc/profile
不重启数据库调整参数
> set global key_buffer_size=10*1024*1024;
# 此操作为全局参数设置，设置完立即生效，重启MySQL后失效，所以若想不重启修改数据库参数并永久生效，可用此操作设置完后再修改对应的配置文件里参数。
登陆数据库后执行系统命令
> system ls -la /tmp
ALL PRIVILEGES包括如下权限
SELECT, INSERT,UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE
一.1.1.3 配置说明
公司集群环境配置文件
1 # Example MySQL config file for very large systems. 2 # 3 # This is for a large system with memory of 1G-2G where the system runs mainly 4 # MySQL. 5 # 6 # MySQL programs look for option files in a set of 7 # locations which depend on the deployment platform. 8 # You can copy this option file to one of those 9 # locations. For information about these locations, see: 10 # http://dev.mysql.com/doc/mysql/en/option-files.html 11 # 12 # In this file, you can use all long options that a program supports. 13 # If you want to know which options a program supports, run the program 14 # with the "--help" option. 15 16 # The following options will be passed to all MySQL clients 17 [client] 18 # password = yourpassword 19 port = 3306 20 socket = /usr/local/mysql/data/mysql.sock 21 # default-character-set=utf8 22 23 # Here follows entries for some specific programs 24 25 # The MySQL server 26 [mysqld] 27 # password = yourpassword 28 server-id =1 # 主机标示，整数 29 read-only =0 # 主机，读写都可以 30 #binlog-do-db =mysql # 需要备份数据，多个写多行 31 #replicate-wild-ignor2-table=mysql.% 32 #binlog-ignore-db =mysql # 不需要备份的数据库，多个写多行 33 port = 3306 34 socket = /usr/local/mysql/data/mysql.sock 35 max_connections=10000 36 max_connect_errors=10000 37 #init-connect='SET NAMES utf8;insert into accesslog.accesslog values(null,connection_id(),now(),substring(user(),1,locate('@',user())-1),substring(user(),locate('@',user())+1,length(user())));' 38 init-connect='SET NAMES utf8' 39 interactive_timeout=31536000 40 #default-character-set=utf8 41 datadir=/r2/mysqldata 42 basedir=/usr/local/mysql 43 log-error=/r2/mysqldata/mysql-error.log 44 pid-file=/r2/mysqldata/mysql.pid 45 #bind-address=127.0.0.1 46 user=mysql 47 #skip-locking 48 key_buffer_size = 384M 49 max_allowed_packet = 64M 50 table_open_cache = 512 51 sort_buffer_size = 2M 52 read_buffer_size = 2M 53 read_rnd_buffer_size = 8M 54 myisam_sort_buffer_size = 64M 55 thread_cache_size = 8 56 query_cache_size = 32M 57 # Try number of CPU's*2 for thread_concurrency 58 #thread_concurrency = 8 59 60 log-slave-updates #这个参数一定要加上，否则不会给更新的记录些到二进制文件里 61 #slave-skip-errors #是跳过错误，继续执行复制操作 62 63 expire_logs_days = 18 64 65 # Don't listen on a TCP/IP port at all. This can be a security enhancement, 66 # if all processes that need to connect to mysqld run on the same host. 67 # All interaction with mysqld must be made via Unix sockets or named pipes. 68 # Note that using this option without enabling named pipes on Windows 69 # (via the "enable-named-pipe" option) will render mysqld useless! 70 # 71 #skip-networking 72 73 # Replication Master Server (default) 74 # binary logging is required for replication 75 log-bin=mysql-bin 76 77 # required unique id between 1 and 2^32 - 1 78 # defaults to 1 if master-host is not set 79 # but will not function as a master if omitted 80 81 # Replication Slave (comment out master section to use this) 82 # 83 # To configure this host as a replication slave, you can choose between 84 # two methods : 85 # 86 # 1) Use the CHANGE MASTER TO command (fully described in our manual) - 87 # the syntax is: 88 # 89 # CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>, 90 # MASTER_USER=<user>, MASTER_PASSWORD=<password> ; 91 # 92 # where you replace <host>, <user>, <password> by quoted strings and 93 # <port> by the master's port number (3306 by default). 94 # 95 # Example: 96 # 97 # CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306, 98 # MASTER_USER='joe', MASTER_PASSWORD='secret'; 99 # 100 # OR 101 # 102 # 2) Set the variables below. However, in case you choose this method, then 103 # start replication for the first time (even unsuccessfully, for example 104 # if you mistyped the password in master-password and the slave fails to 105 # connect), the slave will create a master.info file, and any later 106 # change in this file to the variables' values below will be ignored and 107 # overridden by the content of the master.info file, unless you shutdown 108 # the slave server, delete master.info and restart the slaver server. 109 # For that reason, you may want to leave the lines below untouched 110 # (commented) and instead use CHANGE MASTER TO (see above) 111 # 112 # required unique id between 2 and 2^32 - 1 113 # (and different from the master) 114 # defaults to 2 if master-host is set 115 # but will not function as a slave if omitted 116 #server-id = 2 117 # 118 # The replication master for this slave - required 119 #master-host = <hostname> 120 # 121 # The username the slave will use for authentication when connecting 122 # to the master - required 123 #master-user = <username> 124 # 125 # The password the slave will authenticate with when connecting to 126 # the master - required 127 #master-password = <password> 128 # 129 # The port the master is listening on. 130 # optional - defaults to 3306 131 #master-port = <port> 132 # 133 # binary logging - not required for slaves, but recommended 134 #log-bin=mysql-bin 135 # 136 # binary logging format - mixed recommended 137 binlog_format=mixed 138 139 # Uncomment the following if you are using InnoDB tables 140 #innodb_data_home_dir = /var/lib/mysql 141 #innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend 142 #innodb_log_group_home_dir = /var/lib/mysql 143 # You can set .._buffer_pool_size up to 50 - 80 % 144 # of RAM but beware of setting memory usage too high 145 innodb_buffer_pool_size = 30720M 146 sync_binlog=0 147 query_cache_type= ON 148 query_cache_size= 128M 149 gtid_mode=ON 150 enforce_gtid_consistency=ON 151 slave_parallel_type=LOGICAL_CLOCK 152 slave_parallel_workers=16 153 master_info_repository=TABLE 154 relay_log_info_repository=TABLE 155 relay_log_recovery=ON 156 #innodb_additional_mem_pool_size = 20M 157 # Set .._log_file_size to 25 % of buffer pool size 158 innodb_log_file_size = 256M 159 innodb_log_buffer_size = 8M 160 innodb_flush_log_at_trx_commit = 2 161 #innodb_lock_wait_timeout = 50 162 sql_mode="" 163 [mysqldump] 164 quick 165 max_allowed_packet = 16M 166 167 [mysql] 168 no-auto-rehash 169 # Remove the next comment character if you are not familiar with SQL 170 #safe-updates 171 172 [myisamchk] 173 key_buffer_size = 256M 174 sort_buffer_size = 256M 175 read_buffer = 2M 176 write_buffer = 2M 177 178 [mysqlhotcopy] 179 interactive-timeout
DBA整改后的配置文件
1 # line V1.2 2 # mail: zhenggc@ipanel.cn & lincm@ipanel.cn 3 # data: 2017-10-26 4 # file_name: ipanel-my.cnf 5 # function: my.cnf 6 7 ## optimized mysql configure file my.cnf 8 ## 注意：建议参数可需要根据实际情况作调整 9 ## 本配置文件主要适用于MySQL 5.7.18版本 10 11 # ********* 以下重要参数必须核对 ********* 12 # 1.innodb_flush_log_at_trx_commit=2 13 # 2.sync_binlog = 0 14 # 3.innodb_strict_mode = OFF 15 # 4.innodb_flush_method = O_DIRECT 16 # 5.lower_case_table_names = 0 17 # 6.character-set-server = utf8 18 # 7.sql_mode 配置为空值 19 # 8.server-id =1 修改成对应数值 20 # 9.innodb_buffer_pool_size = 32G 纯mysql server 配置50%和 混合内存配置不低于10G~40% 21 #10.key_buffer_cache=1G 如果有myisam表请配置为1G 22 #11.innodb_data_file_path = ibdata1:1G:autoextend 确认innodb_data_file_path配置是否跟原来一样 23 #12.log_bin = /r2/mysqldata/binlog 旧版本或者之前已配置好如：/r2/mysqldata/slave-bin，请维持原样 24 # ******************************************** 25 [client] 26 port = 3306 27 socket = /r2/mysqldata/mysql.sock 28 #======================================================================= 29 # # MySQL客户端配置 30 #======================================================================= 31 [mysql] 32 prompt="\u@ipanel \R:\m:\s [\d]> " 33 no-auto-rehash 34 #======================================================================= 35 # MySQL服务器全局配置 36 #======================================================================= 37 [mysqld] 38 user = mysql 39 port = 3306 40 server-id = 1 41 tmpdir = /r2/mysqldata 42 datadir = /r2/mysqldata 43 socket = /r2/mysqldata/mysql.sock 44 wait_timeout = 31536000 45 #interactive_timeout = 600 46 sql_mode = 47 #sql_mode 配置为空值 48 skip_name_resolve = 1 49 lower_case_table_names = 0 50 character-set-server = utf8 51 #auto_increment_increment = 1 52 #auto_increment_offset = 1 53 # init_connect = 54 ######################### 性能参数 #################### 55 open_files_limit = 1024000 56 max_connections = 10000 57 max_user_connections=9990 58 max_connect_errors = 100000 59 table_open_cache = 1024 60 max_allowed_packet = 128M 61 thread_cache_size = 64 62 max_heap_table_size = 32M 63 query_cache_type = 0 64 ###global cache ### 65 key_buffer_size = 512M 66 query_cache_size = 0 67 ###session cache ### 68 sort_buffer_size = 8M #排序缓冲 69 join_buffer_size = 4M #表连接缓冲 70 read_buffer_size = 8M #顺序读缓冲 71 read_rnd_buffer_size = 8M #随机读缓冲 72 tmp_table_size = 32M #内存临时表 73 binlog_cache_size = 4M #二进制日志缓冲 74 thread_stack = 256KB #线程的堆栈的大小 75 ######################### binlog设置 ##################### 76 binlog_format = MIXED 77 log_bin = /r2/mysqldata/binlog 78 max_binlog_cache_size = 1G 79 max_binlog_size = 1G 80 expire_logs_days = 30 81 sync_binlog = 0 #重要参数必须修改为0 82 ######################### 复制设置 ######################## 83 log_slave_updates=1 84 #replicate-do-db=User 85 #binlog-ignore-db = test 86 #slave-skip-errors=1146,1032,1062 87 ### GTID 配置 ### 88 gtid_mode=ON 89 enforce-gtid-consistency=true 90 ######################### innodb ########################## 91 default_storage_engine = InnoDB 92 #innodb_data_file_path = ibdata1:1G:autoextend 93 innodb_buffer_pool_size = 32G #系统内存50% 94 innodb_open_files = 1000000 95 innodb_flush_log_at_trx_commit = 2 #线上服务器必须配置为2 96 innodb_file_per_table = 1 97 innodb_lock_wait_timeout = 5 98 # 根据您的服务器IOPS能力适当调整innodb_io_capacity 99 # 一般配普通SSD盘的话，可以调整到 10000 - 20000 100 innodb_io_capacity = 200 101 innodb_io_capacity_max = 20000 102 innodb_flush_method = O_DIRECT 103 innodb_log_file_size = 2G 104 innodb_log_files_in_group = 2 105 innodb_large_prefix = 0 106 innodb_thread_concurrency = 64 107 innodb_strict_mode = OFF 108 innodb_sort_buffer_size = 4194304 109 ######################### log 设置 ##################### 110 log_error = /r2/mysqldata/error.log 111 slow_query_log = 1 112 long_query_time = 10 113 slow_query_log_file = /r2/mysqldata/slow.log 114 #======================================================================= 115 # MySQL mysqldump配置 116 #======================================================================= 117 [mysqldump] 118 quick 119 max_allowed_packet = 32M 120 #======================================================================= 121 # MySQL mysqld_safe配置 122 #======================================================================= 123 [mysqld_safe] 124 log_error = /r2/mysqldata/error.log 125 pid_file = /r2/mysqldata/mysqldb.pid
自用快速启停配置文件
1 [client] 2 port = 3306 3 socket = /usr/local/mysql/data/mysql.sock 4 [mysqld] 5 server-id =2 # 主机标示，整数 6 read-only =0 # 主机，读写都可以 7 port = 3306 8 socket = /usr/local/mysql/data/mysql.sock 9 max_connections=1000 10 max_connect_errors=1000 11 init-connect='SET NAMES utf8' 12 interactive_timeout=31536000 13 datadir=/r2/mysqldata 14 basedir=/usr/local/mysql 15 log-error=/r2/mysqldata/mysql-error.log 16 pid-file=/r2/mysqldata/mysql.pid 17 user=mysql 18 #key_buffer_size = 384M 19 #max_allowed_packet = 64M 20 table_open_cache = 512 21 sort_buffer_size = 2M 22 read_buffer_size = 2M 23 #read_rnd_buffer_size = 8M 24 #myisam_sort_buffer_size = 64M 25 thread_cache_size = 8 26 #query_cache_size = 32M 27 log-slave-updates #这个参数一定要加上，否则不会给更新的记录些到二进制文件里 28 expire_logs_days = 18 29 log-bin=mysql-bin 30 binlog_format=mixed 31 #innodb_buffer_pool_size = 30720M 32 sync_binlog=0 33 query_cache_type= ON 34 #query_cache_size= 128M 35 gtid_mode=ON 36 enforce_gtid_consistency=ON 37 slave_parallel_type=LOGICAL_CLOCK 38 slave_parallel_workers=16 39 master_info_repository=TABLE 40 relay_log_info_repository=TABLE 41 relay_log_recovery=ON 42 #innodb_log_file_size = 256M 43 innodb_log_buffer_size = 8M 44 innodb_flush_log_at_trx_commit = 2 45 sql_mode="" 46 [mysqldump] 47 quick 48 #max_allowed_packet = 16M 49 [mysql] 50 no-auto-rehash 51 [myisamchk] 52 #key_buffer_size = 256M 53 #sort_buffer_size = 256M 54 #read_buffer = 2M 55 #write_buffer = 2M 56 [mysqlhotcopy] 57 interactive-timeout
一.1.1.4 SQL语言
一.1.1.4.1 SQL基础
介绍
全称为结构化查询语言（Structured Query Language）,是一种对关系数据库中数据进行定义和操作的语言方法，用于存取数据以及查询、更新和管理关系数据库系统，同时也是数据库脚本文件的扩展名，是大多数关系数据库管理系统所支持的工业标准。
分类
数据查询语言（DQL）
DQL（Data Query Language），也称为数据检索语言，作用是从表中获取数据，确定数据怎样在应用程序给出，使用动词有SELECT，保留字有WHERE、ORDER BY、GROUP BY、HAVING；
数据操作语言（DML） #常见
DML（Data Manipulation Language）,也称为动作查询语言，使用动词有INSERT、UPDATE、DELETE，分别用于添加、修改、删除表数据等操作；
事物处理语言（TPL）
其语句能确保被DML语句影响的表的所有行及时得以更新，TPL语句包括、BGEIN、TRANSACTION、COMMIT、ROLLBACK.
数据控制语言（DCL） #常见
DCL（Data Control Language），其语句通过GRANT或REVOKE获得许可；
数据定义语言（DDL） #常见
DDL（Data Definition Language），其语句包括动词CREATE和DROP，创表、删表，为表加入索引等；
指针控制语言（CCL）
CCL（Cursor Control Language），语句包括DECLARE CUROR、FETCH INTO、UPDATE WHERE CURRENT，用于对一个或多个表单独行的操作。
一.1.1.4.2 SQL命令
注：
a.进行部分命令操作后应刷新授权 > flush privileges;
b.部分命令用小写执行时会报错；
一.1.1.4.2.1 帮助语句
注：一步一步执行以下命令，可根据帮助命令提供的信息得出所需操作对应执行的命令。
> help;
> help show;
> help revoke;
> help show grants;
> help change master to;
一.1.1.4.2.2 查看语句
查看数据库
> show databases;
查看当前会话库状态
> show status;
查看全局状态
> show global status;
查看变量
> show variables;
匹配查看变量
> show variables like ‘key_buffer%’;
查看正在执行的完整SQL语句
> show full processlist;
匹配查看库
> show databases like '%zi%';
查看当前库
> select database();
查看字符编码
> show variables like '%character%';
查看当前版本
> select version();
查看当前用户
> select user();
查看当前时间
> select now();
查看数据库引擎
> show engines;
查看建库语句
> show create database kazihuo\G;
查看建表语句
> show create table kazihuo\G;
查看表
> show tables;
查看表结构
> desc mysql.user;
一.1.1.4.2.3 查询语句
查询用户信息
mysql> select user,host from mysql.user;
查询授权列表
> SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;
> select * from mysql.user where user='root' \G;
查看指定用户授权信息
mysql> SHOW GRANTS FOR 'root'@'localhost';
查询表数据
> select *from student;
指定字段查询
> select id,name from student;
条件查询
> select *from student limit 2; #只查询2条记录
> select *from student where id=1; #指定字段数据查询
> select *from student where id>2 and id <4;
> select *from student order by age asc; #正序
> select *from student order by age desc; #倒叙
多表查询
> select student.Sno,student.Sname,course.Cname,SC.Grade from student,course,SC where student.Sno=SC.Sno and course.Cno=SC.Cno;
查看sql语句执行计划
> explain select *from test where name=’kazihuo’\G;
一.1.1.4.2.4 使用语句
连接库
> use kazihuo;
一.1.1.4.2.5 创建语句
创建用户
> create user luomr@localhost identified by '000000';
创库
> create database kazihuo;
创建指定字符集数据库（默认为拉丁字符集）
> create database kazihuo_gbk DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;
> create database kazihuo_utf8 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
创表
mysql> create table student(
-> id int(4) not null,
-> name char(20) not null,
-> age int(3) not null default '0',
-> dept varchar(16) default null
-> );
> create table worker(id int,name varchar(10),sex enum('man','woman'));
一.1.1.4.2.6 插入语句
指定字段插入数据
> insert into student(id,name) values(1,'kazihuo');
不指定字段将按照表格式依次插入数据
> insert into student values(2,'ka',23,'boy');
多条记录插入
mysql> insert into student values(3,'zi',3,'man'),(4,'huo',33,'superman');
一.1.1.4.2.7 修改语句
修改表
>update dns_domain_info set domain_info=replace(domain_info,"192.168.53.71","10.79.6.52");
> update dns_domain_info set domain_info='192.168.11.11' where domain_info='192.168.101.106';
指定字段信息修改表信息
> update student set name='tom' where id=2;
修改表名
> rename table teacher to teacher1;
> alter table teacher1 rename to teacher;
一.1.1.4.2.8 删除语句
删除库
> drop database kazihuo;
删除系统账号
> drop kazihuo ‘root’@’localhost’;
> delete from mysql.user where user=’kazihuo’ and host’localhost’; #drop删除无效时使用
删除指定字符串
#delete from homed_iusm.account_info where nickname like "%test%"；
一.1.1.4.2.9 清空语句
清空表中所有内容
> truncate table teacher;
> delete from teacher;
# truncate更快，清空物理文件；delete为逻辑清除，按行删；
一.1.1.4.2.10 字段语句
添加字段
指定位置添加，默认为末尾
> alter table student add qq varchar(20) after id;
> alter table student add qq varchar(20) first;
添加多个字段
> alter table student add qq varchar(20),add grant int;
修改字段
ALTER TABLE 表名
MODIFY 字段名 数据类型 [完整性约束条件…];
ALTER TABLE 表名
CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…];
ALTER TABLE 表名
CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];
一.1.1.4.2.11 权限语句
用户授权
> GRANT ALL PRIVILEGES ON *.* TO 'root'@'luomr' IDENTIFIED BY '123456' WITH GRANT OPTION;
公司授权
> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;
> GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY '123456' WITH GRANT OPTION;
> GRANT ALL PRIVILEGES ON *.* TO 'root'@'127.0.0.1' IDENTIFIED BY '123456' WITH GRANT OPTION;
#所有IP可通过root账号，passwd密码登陆
[root@slave1 src]# mysql -uroot -ppasswd -e "GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'passwd' WITH GRANT OPTION"
[root@slave1 src]# mysql -uroot -ppasswd -e "GRANT ALL PRIVILEGES ON *.* TO 'root'@'127.0.0.1' IDENTIFIED BY 'passwd' WITH GRANT OPTION"
[root@slave1 src]# mysql -uroot -ppasswd -e "GRANT ALL PRIVILEGES ON *.* TO 'root'@''localhost'' IDENTIFIED BY 'passwd' WITH GRANT OPTION"
局域网内主机授权
# 百分号匹配法
> GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.11.%' IDENTIFIED BY '123456' WITH GRANT OPTION;
# 子网掩码配置法
> GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.11.0/24' IDENTIFIED BY '123456' WITH GRANT OPTION;
一.1.1.4.2.12 密码操作
修改密码
# mysqladmin -uroot -p000000 password '111111'
> update mysql.user set authentication_string=password('newpass') where user='root' and host='localhost';
> set password = password('xxxxxxxx');
> flush privileges;
忘记密码
# /etc/init.d/mysqld stop
# /usr/local/mysql/bin/mysqld_safe --skip-grant-tables --user=mysql &
# 多实例跳过密码启动
#/usr/local/mysql/bin/mysqld_safe --skip-grant-tables --defaults-file=/data/3306/my.cnf &
进入mysql后用update方式设置密码
> flush privileges;
若重新登录mysql时仍不需要密码，则需重启mysql
一.1.1.5 数据迁移
一.1.1.6 数据备份
刷新binlog
# mysqldump -uroot -p000 flush-log
锁表
> flush table with read lock;
解锁
> unlock tables;
一.1.1.6.1 备份命令
生产场景常规备份
# mysqldump -uroot -p000 --set-gtid-purged=OFF --master-data=2 -A -B |gzip> /tmp/bak`date +%F-%T`.sql.gz
# mysqldump -uroot -p000 --all-databases --routines --events >/r2/baksql_`date +"%Y%m%d"`.sql
分引擎备份（专业DBA提供）
myisam
# mysqldump -uroot -p000 -A -E -R -F -x --flush-privileges --master-data=1 --hex-blob --set-gtid-purged=OFF >/tmp/`date +%F-%T`_all.sql
innodb（推荐使用）
# mysqldump -uroot -p000 -A -E -R -F --single-transaction --flush-privileges --master-data=1 --hex-blob --set-gtid-purged=OFF|gzip >/tmp/all_`date +%F-%T`.sql.gz
# mysqldump -uroot -p000 -A -E -R -F --single-transaction --flush-privileges --master-data=1 --hex-blob --set-gtid-purged=OFF >/tmp/all_`date +%Y%m%d`.sql
分库备份
# mysqldump -uroot -p000 --set-gtid-purged=OFF -B $db > "$bakpath"/bak_"$db"_`date +%F-%T`.sql
# 配合for语句实现分库备份
脚本备份
# mysql -uroot -p000 -e "show databases;"|grep -ivE "database|infor|performance|sys|mysql"|sed 's/^/mysqldump -uroot -p000 -B /g'
命令行执行命令实现分库备份并压缩
# mysql -uroot -p000 -e "show databases;"|grep -ivE "database|infor|performance|sys|mysql"|sed -r 's#^([a-zA-Z].*$)#mysqldump -uroot -p000 --set-gtid-purged=OFF -B \1|gzip >/tmp/\1.sql.gz#g'|bash
SQL精简（过滤注释）
# egrep -v "#|\*|--|^$" all.sql
-set-gtid-purged=OFF 说明
当执行mysqldump不加以上参数时发出警告
虽然能备份成功，但是在之后的数据恢复时报错
需注释报错行方可成功导入数据，而当加了以上参数时，将不会出现以上问题，同时数据也可正常导入。
MySQL5.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).
全局事务标识符(GTID)是一个惟一的标识符，它与在源服务器上提交的每个事务相关联(主)。
一.1.1.6.2 mysqldump
部分常用参数说明
-u --user
指定连接的用户名；
-p --password
连接数据库密码
-h --host
需要导出的主机信息
-P --port
连接数据库端口号
-A --all-databases
导出全部数据库；
-B --databases
表示接多个库，且增加use db、create db的信息（即恢复数据时不需指定库、表）；
-S --socket
指定连接mysql的socket文件位置；
-x --lock-all-tables
提交请求锁定所有数据库中的所有表，以保证数据的一致性。这是一个 全局读锁，并且自动关闭--single-transaction和--lock-tables选项；
-Y --all-tablespaces
导出全部表空间；
-F --flush-logs
开始导出之前刷新日志；
若一次导出多个数据库(使用选项--databases或者--all-databases)， 将会逐个数据库刷新日志。除使用 --lock-all-tables或者--master-data 外。在这种情况下，日志将会被刷新一次，相应的所有表同时被锁定。因此， 如果打算同时导出和刷新日志应该使用--lock-all-tables或者 --master-data和--flush-logs；
-d --no-data
不导出任何数据，只导出数据库表结构；
-t --no-create-info
只备份数据；
-V --version
输出mysqldump版本信息并退出；
-X --xml
导出XML格式；
--default-character-set
设置默认字符集，默认值为utf8；
eg:mysqldump -uroot -p --all-databases --default-character-set=latin1
--compact
导出更少的输出信息(用于调试)，去掉注释和头尾等结构；
--master-data
将binlog位置和文件名追加输入文件，如果为1，将会输出CHANGE MASTER命令；如果为2，输出的 CHANGE MASTER命令前添加注释信息；
--hex-blob
使用十六进制格式导出二进制字符串字段。如果有二进制数据就必须使 用该选项，影响到的字段类型有BINARY、VARBINARY、BLOB；
--flush-privileges
在导出mysql数据库之后，发出一条FLUSH PRIVILEGES语句。为了正确 恢复，用于导出mysql数据库和依赖mysql数据库数据的任何时候；
--triggers
导出触发器（默认启用，用--skip-triggers禁用）；
-R --routines
导出存储过程以及自定义函数；
-E --events
导出事件；
-y --no-tablespaces
不导出任何表空间信息；
一.1.1.7 数据恢复
一.1.1.7.1 恢复操作
恢复方式
方法一：
mysql>use $db;
mysql>set names utf8;
mysql>source /tmp/bak.sql;
方法二：
#mysql -uroot -p000 $db < bak.sql
一.1.1.7.2 bin-log
基础说明
MySQL的binlog存在于数据库的数据目录下；
其作用是：
用来记录mysql内部增删改查等对mysql数据库有更新的内容的记录。
参数说明
-d
截取指定库的binlog；
解析binlog日志
[root@Q5 /r2/mysqldata]# mysqlbinlog mysql-bin.000003 > bin.sql
导出指定库的对应sql操作
# mysqlbinlog -d kazihuo mysql-bin.000010 >kazihuo.sql
一.1.1.7.3 增量恢复
一.1.1.7.3.1 基于位置点
指定开始位置和结束位置
# mysqlbinlog msql-bin.000020 --start-position=510 --stop-position=1312 -r pos.sql
# 输出初始位置510，结束位置1312的所有binlog日志到pos.sql;
注：结尾日志点将不被包含（即输出1312pos之前的binlog），位置信息点要实际存在。
-r 选项相当于重定向；
指定开始位置到文件结束
# mysqlbinlog msql-bin.000020 --start-position=510 -r pos510-end.sql
同时也可指定库名输出binlog
# mysqlbinlog msql-bin.000020 --start-position=510 -r pos510-end.sql -d kazihuo
从文件开头到指定结束位置
一.1.1.7.3.2 基于时间点
一.1.1.8 多实例化
注：多实例执行mysql命令时要指定sock文件。
一.1.1.8.1 配置方案
通过配置多个配置文件及多个启动程序实现多实例方案；
配置：
[root@Q1 /]# tree /data/
1 /data/ 2 3 ├── 3306 4 5 │ ├── data #数据文件 6 7 │ ├── my.cnf #配置文件 8 9 │ └── mysql #启动文件 10 11 └── 3307 12 13 ├── data 14 15 ├── my.cnf 16 17 └── mysql
配置文件对比
一.1.1.8.2 安装部署
# groupadd mysql
# useradd -r -s /sbin/nologin g mysql mysql
# tar axvf mysql-5.7.18
# mv mysql-5.7.18 /usr/local/mysql
# chown -R mysql.mysql /data
# find /data/ -type f -name “mysql”|xargs ls -l
# find /data/ -type f -name “mysql”|xargs chmod +x
# cd /usr/local/mysql/bin
#./mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/3306/data
#./mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/3307/data
常用初始化命令
# ./mysqld --initialize-insecure --user=mysql --datadir=/r2/mysqldata --basedir=/usr/local/mysql
环境变量
方式一
# echo ‘export PATH=/usr/local/mysql/bin:$PATH ’ >> /etc/profile
# echo后必须为单引号
# source /etc/profile
方式二
把/usr/local/mysql/bin下面命令cp到全局系统命令路径/usr/local/sbin也可，或者做link
*************************************************************
a.初始化数据库原因：
初始化的目的是创建基础的数据库文件（生成mysql库表等）；
初始化后对应实例数据目录下生成文件；
一.1.1.8.3 服务启停
注：尽量不要野蛮的方式杀死数据库，生产高并发环境可能会引起数据丢失。
多实例启动文件的启动mysql：
# mysql_safe --defaults-file=/data/3306/my.cnf 2>&1 >/dev/null &
# mysql_safe --defaults-file=/data/3307/my.cnf 2>&1 >/dev/null &
多实例启动文件的停止mysql（平滑停止）：
# mysqladmin -uroot -p000000 -S /data/3306/mysql.sock shutdown
# mysqladmin -uroot -p000000 -S /data/3307/mysql.sock shutdown
一.1.1.8.4 服务登陆
#mysql -uroot -p00000 -S/data/3306/mysql.sock
#mysql -uroot -p00000 -S/data/3307/mysql.sock
一.1.1.9 问题说明
一.1.1.9.1 登陆报错
问题描述
数据库正常启动，登陆时显示
[root@M-mysql ~]# mysql -uroot -p000000
-bash:mysql:未找到命令
解决方案
配置了mysql的环境变量未生效，执行# source /etc/profile
一.1.1.9.2 表操作报错
问题描述
登陆数据库打开表时报错：can’t create/write to file ‘tmp#sql_23e6_0MYI’
解决方案
# mkdir /var/lib/mysql/tmp
# chown mysql.mysql /var/lib/mysql/tmp
# vim /etc/my.cnf
tmp=/var/lib/mysql/tmp
# /etc/init.d/mysql restart
#查看指定目录大小：
# df -h /tmp
以上问题实操解决
一.1.1.9.3 关机卡死
问题描述
虚拟机部署了mysql5.7之后关机卡在如下页面：
解决方案
# swapon -s
# free -h
# df -h /dev/shm
# grep -i huge /proc/meminfo
查看swap使用超过50%
出现此问题可能是服务器内存不足导致，可增大内存或者修改my.cnf将参数要求值降低，可解决此问题。
一.1.1.9.4 删除binlog无法启动
问题描述
当磁盘空间不足，删除mysqldata下的binlog后导致数据库无法正常启动。
解决方案
清空binlog索引文件即可
[root@M-mysql /r2/mysqldata]# > mysql-bin.index
一.1.1.10 主从同步
一.1.1.10.1 主从复制原理
a.slave数据库上执行start slave，开启主从复制开关；
b.slave的I/O线程通过在master上的授权的复制用户权限请求连接master服务器，并请求从指定binlog日志文件的指定位置（日志文件名和位置就是在配置主从复制时执行change master命令时指定的）之后发送binlog日志内容；
c.master收到请求后，负责复制的I/O线程根据请求提供相应binlog给slave；
d.slave收到信息后，将binlog写入到自身中继日志（Relay Log）文件（MySQL-relay-bin.xxx）的最末端，并将新的binlog文件名和位置记录到master-info文件（便于下次读取master的binlog日志时能告诉master服务器需要从新binlog日志的哪个文件哪个位置开始请求新的binlog日志内容）。
e.slave通过SQL线程将log文件内容解析成SQL语句并执行，应用完毕后清理用过的日志；
说明
从服务器上生成的master.info是给I/O线程使用，relay-log.INFO给SQL线程使用。
一.1.1.10.2 实现主从同步
主库操作
建立从库复制的账号rep
> grant replication slave on *.* to 'rep'@'192.168.11.%' identified by 'master';
> show master status;
+------------------+----------+--------------+------------------+---------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+---------------------------------------------+
| mysql-bin.000021 | 791| | fda28692-9e31-11e7-bf34-000c2907c998:1-1132 |
+------------------+----------+--------------+------------------+---------------------------------------------+
# mysqldump -uroot -pmaster -A -E -R -F --single-transaction --flush-privileges --master-data=1 --hex-blob --set-gtid-purged=OFF >/tmp/all_`date +%Y%m%d`.sql
将备份的sql同步到从库
说明：当主库备份时加了--master-data=1时，在生成的sql里面包含了对应的binlog信息及指定的position，所有在从库上执行CHANGE MASTER TO时可省略MASTER_LOG_FILE='mysql-bin.000021',MASTER_LOG_POS=791;
从库操作
CHANGE MASTER TO
MASTER_HOST='192.168.11.17',
MASTER_USER='rep',
MASTER_PASSWORD='master',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000021',
MASTER_LOG_POS=791;
> start slave;
检验是否成功
> show slave status;
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0
当显示以上信息，同时在master上对数据进行操作时，对应的slave数据库有着对应操作的结果体现。
-------------------------------------------------------------
转载请保留此段声明，且在文章页面明显位置给出原文链接，谢谢！
------------------------------------------------------------------------------
如果觉得这篇文章对你有小小的帮助的话，记得在右下角点个“推荐”哦，博主在此感谢！
------------------------------------------------------------------------------