MYSQL汇总

卡子火 2017-10-22

mysql SQL 日志 数据库 配置 grant 字符集 Create binlog

一.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界面安装,需要SELECTINSERTUPDATEDELETECREATEDROP(较危险)权限;

> grant select,insert,update,delete,create,drop on blog.* to ‘blog’@’10.0.0.%’ identified by ‘000000’;

b.生成数据库表后,要收回CREATEDROP权限;

> 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 当发出没有WHERELIMIT关键字的UPDATEDELETE时,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
my.cnf

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
my.cnf

自用快速启停配置文件

 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
my.cnf

一.1.1.4 SQL语言

一.1.1.4.1 SQL基础

介绍

全称为结构化查询语言(Structured Query Language,是一种对关系数据库中数据进行定义和操作的语言方法,用于存取数据以及查询、更新和管理关系数据库系统,同时也是数据库脚本文件的扩展名,是大多数关系数据库管理系统所支持的工业标准。

分类

数据查询语言(DQL

DQLData Query Language),也称为数据检索语言,作用是从表中获取数据,确定数据怎样在应用程序给出,使用动词有SELECT,保留字有WHEREORDER BYGROUP BYHAVING

数据操作语言(DML    #常见

DMLData Manipulation Language,也称为动作查询语言,使用动词有INSERTUPDATEDELETE,分别用于添加、修改、删除表数据等操作;

事物处理语言(TPL

其语句能确保被DML语句影响的表的所有行及时得以更新,TPL语句包括、BGEINTRANSACTIONCOMMITROLLBACK.

数据控制语言(DCL    #常见

DCLData Control Language),其语句通过GRANTREVOKE获得许可;

数据定义语言(DDL    #常见

DDLData Definition Language),其语句包括动词CREATEDROP,创表、删表,为表加入索引等;

指针控制语言(CCL

CCLCursor Control Language),语句包括DECLARE CURORFETCH INTOUPDATE 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版本后,官方加入了全局事物IDGTID)来强化数据库的主备一致性,故障恢复及容错能力;

官方解释:

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

基础说明

MySQLbinlog存在于数据库的数据目录下;

其作用是:

用来记录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
data

配置文件对比

一.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.slaveI/O线程通过在master上的授权的复制用户权限请求连接master服务器,并请求从指定binlog日志文件的指定位置(日志文件名和位置就是在配置主从复制时执行change master命令时指定的)之后发送binlog日志内容;

c.master收到请求后,负责复制的I/O线程根据请求提供相应binlogslave

d.slave收到信息后,将binlog写入到自身中继日志(Relay Log)文件(MySQL-relay-bin.xxx)的最末端,并将新的binlog文件名和位置记录到master-info文件(便于下次读取masterbinlog日志时能告诉master服务器需要从新binlog日志的哪个文件哪个位置开始请求新的binlog日志内容)。

e.slave通过SQL线程将log文件内容解析成SQL语句并执行,应用完毕后清理用过的日志;

说明

从服务器上生成的master.info是给I/O线程使用,relay-log.INFOSQL线程使用。

一.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数据库有着对应操作的结果体现。

-------------------------------------------------------------

作者:罗穆瑞
出处:http://www.cnblogs.com/kazihuo/

转载请保留此段声明,且在文章页面明显位置给出原文链接,谢谢!

------------------------------------------------------------------------------

如果觉得这篇文章对你有小小的帮助的话,记得在右下角点个“推荐”哦,博主在此感谢!

------------------------------------------------------------------------------

登录 后评论
下一篇
云栖号资讯小编
16093人浏览
2020-07-13
相关推荐
mysql安装方式汇总
806人浏览
2017-11-09 08:05:03
阿里云使用问题汇总
946人浏览
2018-01-27 11:23:56
mysql几个常见问题汇总
729人浏览
2017-11-07 20:42:00
mysql 错误汇总
600人浏览
2017-11-16 18:09:00
mysql 获取刚插入行id汇总
551人浏览
2015-04-20 14:08:00
MySQL修改密码方法汇总
1761人浏览
2018-08-11 08:20:12
mysql 知识碎片汇总
885人浏览
2019-05-08 14:52:27
0
0
0
965