MySQL学习

MySQL

一、window安装


x
1.如果想要让MySQL安装在指定目录，那么就将解压后的文件夹移动到指定目录，如：C:\mysql-5.6.40-winx64
2.添加环境变量
3.初始化：mysqld --initialize-insecure
4.把mysql服务制作成Windows服务
    注意：--install前，必须用mysql启动命令的绝对路径
    # 制作MySQL的Windows服务，在终端执行此命令：
    "c:\mysql-5.6.40-winx64\bin\mysqld" --install
    # 移除MySQL的Windows服务，在终端执行此命令：
    "c:\mysql-5.6.40-winx64\bin\mysqld" --remove
    注册成服务之后，以后再启动和关闭MySQL服务时，仅需执行如下命令：
    # 启动MySQL服务
    net start mysql
    # 关闭MySQL服务
    net stop mysql
5.启动mysql服务：mysqld
6.启动mysql客户端并连接mysql服务端（新开一个cmd窗口）
命令：mysql -uroot -p
初始化密码：
C:\Users\mjj>mysqladmin -uroot -p password "123"  #设置初始密码 由于原密码为空，因此-p可以不用 Mysql版本的不同，可能执行这句指令不起
作用，请使用update mysql.user set authentication_string =password('') where User='root'; 
更改密码：
C:\Users\mjj>mysqladmin -uroot -p"123" password "456"  #修改mysql密码,因为已经有密码了，所以必须输入原密码才能设置新密码
#破解密码 停止服务
#跳过授权表
C:\WINDOWS\system32>mysqld --skip-grant-tables
2018-06-09 17:12:38 0 [Warning] Insecure configuration for --secure-file-priv: Current value does not restrict locatio
n of generated files. Consider setting it to a valid, non-empty path.
2018-06-09 17:12:38 0 [Note] mysqld (mysqld 5.6.40) starting as process 6052 ...4.
# 现在可以空密码登录
update mysql.user set authentication_string =password('') where User='root'; 
flush privileges;
C:\Users\Sky>tasklist| findstr mysql
mysqld.exe                    3940 Services                   0     75,508 K
C:\Users\Sky>taskkill /F /PID 3940
mysql> \s
--------------
mysql  Ver 14.14 Distrib 5.7.26, for Linux (x86_64) using  EditLine wrapper
Connection id:      129
Current database:   skym
Current user:       root@localhost
SSL:            Cipher in use is DHE-RSA-AES256-SHA
Current pager:      stdout
Using outfile:      ''
Using delimiter:    ;
Server version:     5.7.26-log MySQL Community Server (GPL)
Protocol version:   10
Connection:     127.0.0.1 via TCP/IP
Server characterset:    latin1
Db     characterset:    utf8
Client characterset:    utf8
Conn.  characterset:    utf8
TCP port:       3306
Uptime:         21 hours 24 min 15 sec
Threads: 6  Questions: 4279  Slow queries: 281  Opens: 379  Flush tables: 1  Open tables: 368  Queries per second avg: 0.055
--------------

二、centos服务器安装

1、检查


xxxxxxxxxx
//这个命令就会查看该操作系统上是否已经安装了mysql数据库
[sky@LENOVO-LA0X1175 bin]$ rpm -qa | grep mysql
qt-mysql-4.8.7-2.el7.x86_64
akonadi-mysql-1.9.2-4.el7.x86_64

2、删除原有安装软件


xxxxxxxxxx
[sky@LENOVO-LA0X1175 bin]# rpm -e mysql　　// 普通删除模式
[sky@LENOVO-LA0X1175 bin]# rpm -e --nodeps mysql　　// 强力删除模式，如果使用上面命令删除时，提示有依赖的其它文件，则用该命令可以对其进行强力删除

3、安装MySQL源


xxxxxxxxxx
// 检查当前仓库是否有mysql的安装源
[sky@LENOVO-LA0X1175 bin]$ yum list | grep mysql-server
// 从官网下载最新的源
// https://dev.mysql.com/downloads/repo/yum/
// https://dev.mysql.com/doc/refman/5.6/en/linux-installation-yum-repo.html
[sky@LENOVO-LA0X1175 bin]$ wget https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm
[sky@LENOVO-LA0X1175 bin]$  sudo yum localinstall mysql80-community-release-el7-3.noarch.rpm

4、检查MySQL源是否安装成功


xxxxxxxxxx
[sky@LENOVO-LA0X1175 ~]$ yum repolist enabled | grep "mysql.*-community.*"
mysql-connectors-community/x86_64 MySQL Connectors Community                 105
mysql-tools-community/x86_64      MySQL Tools Community                       89
mysql80-community/x86_64          MySQL 8.0 Community Server                  99

5、选择MySQL的安装版本


xxxxxxxxxx
[sky@LENOVO-LA0X1175 ~]$ yum repolist all | grep mysql
mysql-cluster-7.5-community/x86_64 MySQL Cluster 7.5 Community      禁用
mysql-cluster-7.5-community-source MySQL Cluster 7.5 Community - So 禁用
mysql-cluster-7.6-community/x86_64 MySQL Cluster 7.6 Community      禁用
mysql-cluster-7.6-community-source MySQL Cluster 7.6 Community - So 禁用
mysql-cluster-8.0-community/x86_64 MySQL Cluster 8.0 Community      禁用
mysql-cluster-8.0-community-source MySQL Cluster 8.0 Community - So 禁用
mysql-connectors-community/x86_64  MySQL Connectors Community       启用:    105
mysql-connectors-community-source  MySQL Connectors Community - Sou 禁用
mysql-tools-community/x86_64       MySQL Tools Community            启用:     89
mysql-tools-community-source       MySQL Tools Community - Source   禁用
mysql-tools-preview/x86_64         MySQL Tools Preview              禁用
mysql-tools-preview-source         MySQL Tools Preview - Source     禁用
mysql55-community/x86_64           MySQL 5.5 Community Server       禁用
mysql55-community-source           MySQL 5.5 Community Server - Sou 禁用
mysql56-community/x86_64           MySQL 5.6 Community Server       禁用
mysql56-community-source           MySQL 5.6 Community Server - Sou 禁用
mysql57-community/x86_64           MySQL 5.7 Community Server       禁用
mysql57-community-source           MySQL 5.7 Community Server - Sou 禁用
mysql80-community/x86_64           MySQL 8.0 Community Server       启用:     99
mysql80-community-source           MySQL 8.0 Community Server - Sou 禁用
# 禁用MySQL8
[sky@LENOVO-LA0X1175 ~]$ sudo yum-config-manager --disable mysql80-community
# 启用MySQL7
[sky@LENOVO-LA0X1175 ~]$ sudo yum-config-manager --enable mysql57-community

6、安装mysql

yum install mysql-community-server

7、启动MySQL

systemctl start mysql

usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid --user=mysql

sudo /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid --defaults-extra-file=/etc/my.cnf --user=mysql

8、查看初始化MySQL密码

grep 'temporary password' /var/log/mysqld.log

9、登录、修改初始密码


xxxxxxxxxx
alter user 'root'@'localhost' identified by 'SKYsky@1806';
mysql> alter user 'root'@'localhost' identified by 'tepia@123.com';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
mysql> select version();
+------------+
| version()  |
+------------+
| 5.7.26-log |
+------------+
1 row in set (0.00 sec)
mysql> set global validate_password_policy=0;
Query OK, 0 rows affected (0.00 sec)
mysql> alter user 'root'@'localhost' identified by 'tepia@123.com';
Query OK, 0 rows affected (0.00 sec)

10、防火墙的开放

iptables -A IN_public_allow -p tcp -m tcp --dport 3306 -m conntrack --ctstate NEW -j ACCEPT

配置文件存放位置：/etc/sysconfig/iptables

11、开启远程MySQL连接

grant all privileges on *.* to root@'10.44.47.%' identified by 'SKYsky@1806';

12、sql_mode的设置

该问题是因为MySQL Server 默认开启了 sql_mode=only_full_group_by 模式，此模式要求 group by 字段必须出现在查询项中（select），否则就会报出该错误。


xxxxxxxxxx
mysql> SELECT @@GLOBAL.sql_mode;
+------------------------------------------------------------------------------------------------------------------------+
| @@GLOBAL.sql_mode                                                                                                      |
+------------------------------------------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)


xxxxxxxxxx
[sky@LENOVO-LA0X1175 ~]$ cat /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

三、ubuntu安装mysql

教程

四、MySQL优化

先查看所有的日志配置情况：

show variables like '%log%';

1、开启慢日志


xxxxxxxxxx
# 开启慢查询
# 直接在配置文件中加
slow_query_log=1
slow_query_log_file=/tmp/mysql_slow.log
long_query_time=1
log_output=FILE
# 一次性
mysql> show variables like "%slow_query_log%";
+---------------------+---------------------+
| Variable_name       | Value               |
+---------------------+---------------------+
| slow_query_log      | ON                  |
| slow_query_log_file | /tmp/mysql_slow.log |
+---------------------+---------------------+
2 rows in set (0.00 sec)
set global slow_query_log=on;

2、确认慢日志是否开启成功


xxxxxxxxxx
mysql> SHOW VARIABLES LIKE '%slow_query_log%';
+---------------------+---------------------+
| Variable_name       | Value               |
+---------------------+---------------------+
| slow_query_log      | ON                  |
| slow_query_log_file | /tmp/mysql_slow.log |
+---------------------+---------------------+
2 rows in set (0.00 sec)

3、mysqldumpslow的使用


xxxxxxxxxx
mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log     #得到返回记录集最多的10个SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/atguigu-slow.log     #得到访问次数最多的10个SQL
mysqldumpslow -s t -t 10 -g "LEFT JOIN" /var/lib/mysql/atguigu-slow.log   #得到按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log | more      #结合| more使用，防止爆屏情况

4、show profile分析sql


xxxxxxxxxx
mysql> show variables like "%pro%";
+------------------------------------------+-------+
| Variable_name                            | Value |
+------------------------------------------+-------+
| check_proxy_users                        | OFF   |
| have_profiling                           | YES   |
| mysql_native_password_proxy_users        | OFF   |
| performance_schema_max_program_instances | -1    |
| profiling                                | OFF   |
| profiling_history_size                   | 15    |
| protocol_version                         | 10    |
| proxy_user                               |       |
| sha256_password_proxy_users              | OFF   |
| slave_compressed_protocol                | OFF   |
| stored_program_cache                     | 256   |
+------------------------------------------+-------+
11 rows in set (0.00 sec)
mysql> set profiling=ON;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show profiles;  
+----------+------------+--------------------------------+  
| Query_ID | Duration   | Query                          |  
+----------+------------+--------------------------------+  
|        1 | 0.00253600 | show variables like '%profil%' |  
|        2 | 0.00138150 | select count(*) from customer  |  
|        3 | 0.00228600 | show warnings                  |  
+----------+------------+--------------------------------+ 
mysql> show profile for query 8;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000207 |
| checking permissions | 0.000010 |
| checking permissions | 0.000003 |
| checking permissions | 0.000003 |
| checking permissions | 0.000007 |
| Opening tables       | 0.000034 |
| init                 | 0.000093 |
| System lock          | 0.000013 |
| optimizing           | 0.000039 |
| statistics           | 0.000161 |
| preparing            | 0.000031 |
| executing            | 0.000004 |
| Sending data         | 1.448112 |
| end                  | 0.000016 |
| query end            | 0.000017 |
| closing tables       | 0.000015 |
| freeing items        | 0.000092 |
| logging slow query   | 0.000063 |
| cleaning up          | 0.000029 |
+----------------------+----------+
19 rows in set, 1 warning (0.00 sec)
set @query_id=8; 
SELECT
    STATE,
    SUM(DURATION) AS Total_R,
    ROUND(
        100 * SUM(DURATION) / (
            SELECT
                SUM(DURATION)
            FROM
                INFORMATION_SCHEMA.PROFILING
            WHERE
                QUERY_ID = @query_id
        ),
        2
    ) AS Pct_R,
    COUNT(*) AS Calls,
    SUM(DURATION) / COUNT(*) AS "R/Call"
FROM
    INFORMATION_SCHEMA.PROFILING
WHERE
    QUERY_ID = @query_id
GROUP BY
    STATE
ORDER BY
    Total_R DESC;
+----------------------+----------+-------+-------+--------------+
| STATE                | Total_R  | Pct_R | Calls | R/Call       |
+----------------------+----------+-------+-------+--------------+
| Sending data         | 1.448112 |  NULL |     1 | 1.4481120000 |
| starting             | 0.000207 |  NULL |     1 | 0.0002070000 |
| statistics           | 0.000161 |  NULL |     1 | 0.0001610000 |
| init                 | 0.000093 |  NULL |     1 | 0.0000930000 |
| freeing items        | 0.000092 |  NULL |     1 | 0.0000920000 |
| logging slow query   | 0.000063 |  NULL |     1 | 0.0000630000 |
| optimizing           | 0.000039 |  NULL |     1 | 0.0000390000 |
| Opening tables       | 0.000034 |  NULL |     1 | 0.0000340000 |
| preparing            | 0.000031 |  NULL |     1 | 0.0000310000 |
| cleaning up          | 0.000029 |  NULL |     1 | 0.0000290000 |
| checking permissions | 0.000023 |  NULL |     4 | 0.0000057500 |
| query end            | 0.000017 |  NULL |     1 | 0.0000170000 |
| end                  | 0.000016 |  NULL |     1 | 0.0000160000 |
| closing tables       | 0.000015 |  NULL |     1 | 0.0000150000 |
| System lock          | 0.000013 |  NULL |     1 | 0.0000130000 |
| executing            | 0.000004 |  NULL |     1 | 0.0000040000 |
+----------------------+----------+-------+-------+--------------+
16 rows in set, 17 warnings (0.01 sec)
set profiling=OFF;

5、禁用缓存查询


xxxxxxxxxx
SELECT sql_no_cache
    count(*)
FROM
    immigration i
LEFT JOIN reservoirs r ON r.reservoirs_code = i.reservoirs_ennmcd
LEFT JOIN area a ON a.area_code = r.areaCode
LEFT JOIN area a1 ON a1.area_code = i.area_code
WHERE
    i.is_valid = 1
AND shzt = 0
AND is_back = 0

6、开启缓存

query_cache_type=1

7、优化工具PROCEDURE ANALYSE介绍


xxxxxxxxxx
mysql> SELECT name FROM `immigration` PROCEDURE analyse()\G;
*************************** 1. row ***************************
             Field_name: skym.immigration.name
              Min_value:   张兰 
              Max_value: 龚黄钦
             Min_length: 6
             Max_length: 31
       Empties_or_zeros: 0
                  Nulls: 0
Avg_value_or_avg_length: 8.8410
                    Std: NULL
      Optimal_fieldtype: TINYTEXT NOT NULL
1 row in set, 1 warning (0.28 sec)

MySQL5.6和5.7配置文件


xxxxxxxxxx
[client]
user=david
password=88888888
[mysqld]
########basic settings########
server-id = 11 
port = 3306
user = mysql
bind_address = 10.166.224.32
autocommit = 0
character_set_server=utf8mb4
skip_name_resolve = 1
max_connections = 800
max_connect_errors = 1000
datadir = /data/mysql_data
transaction_isolation = READ-COMMITTED
explicit_defaults_for_timestamp = 1
join_buffer_size = 134217728
tmp_table_size = 67108864
tmpdir = /tmp
max_allowed_packet = 16777216
sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER"
interactive_timeout = 1800
wait_timeout = 1800
read_buffer_size = 16777216
read_rnd_buffer_size = 33554432
sort_buffer_size = 33554432
########log settings########
log_error = error.log
slow_query_log = 1
slow_query_log_file = slow.log
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
log_slow_slave_statements = 1
log_throttle_queries_not_using_indexes = 10
expire_logs_days = 90
long_query_time = 2
min_examined_row_limit = 100
########replication settings########
master_info_repository = TABLE
relay_log_info_repository = TABLE
log_bin = bin.log
sync_binlog = 1
gtid_mode = on
enforce_gtid_consistency = 1
log_slave_updates
binlog_format = row 
relay_log = relay.log
relay_log_recovery = 1
binlog_gtid_simple_recovery = 1
slave_skip_errors = ddl_exist_errors
########innodb settings########
innodb_page_size = 8192
innodb_buffer_pool_size = 6G
innodb_buffer_pool_instances = 8
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_lru_scan_depth = 2000
innodb_lock_wait_timeout = 5
innodb_io_capacity = 4000
innodb_io_capacity_max = 8000
innodb_flush_method = O_DIRECT
innodb_file_format = Barracuda
innodb_file_format_max = Barracuda
innodb_log_group_home_dir = /redolog/
innodb_undo_directory = /undolog/
innodb_undo_logs = 128
innodb_undo_tablespaces = 3
innodb_flush_neighbors = 1
innodb_log_file_size = 4G
innodb_log_buffer_size = 16777216
innodb_purge_threads = 4
innodb_large_prefix = 1
innodb_thread_concurrency = 64
innodb_print_all_deadlocks = 1
innodb_strict_mode = 1
innodb_sort_buffer_size = 67108864 
########semi sync replication settings########
plugin_dir=/usr/local/mysql/lib/plugin
plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
loose_rpl_semi_sync_master_enabled = 1
loose_rpl_semi_sync_slave_enabled = 1
loose_rpl_semi_sync_master_timeout = 5000
[mysqld-5.7]
innodb_buffer_pool_dump_pct = 40
innodb_page_cleaners = 4
innodb_undo_log_truncate = 1
innodb_max_undo_log_size = 2G
innodb_purge_rseg_truncate_frequency = 128
binlog_gtid_simple_recovery=1
log_timestamps=system
transaction_write_set_extraction=MURMUR32
show_compatibility_56=on