MySQL数据库基本概念

MySQL数据库基本概念

MySQL 5.7 安装

Percona-Server-5.7.19-17-Linux.x86_64.ssl101

基于redhat 6.3操作系统

安装依赖

yum -y install gcc glibc libaio libstdc++ libstdc libncurses ld-linux

下载mysql

解压

tar -xvf /usr/local/Percona-Server-5.7.19-17-Linux.x86_64.ssl101.tar.gz -C /usr/local/

建立软链接 方便使用

cd /usr/local/
ln -s /usr/local/Percona-Server-5.7.19-17-Linux.x86_64.ssl101 mysql

添加mysql 用户

groupadd mysql 
useradd -r -g mysql mysql

修改该软连接属性

chown -R mysql:mysql    /usr/local/mysql

添加环境变量

echo 'export PATH=/usr/local/mysql/bin:$PATH' >> /etc/profile
source /etc/profile

创建data和binlog 目录

mkdir -p /data/mysql/data  -----------------工作目录
mkdir -p /data/mysql/binlog  -----------------工作目录
// mkdir -p /data/mysql/{data,binlog}

修改他们的权限

chown -R mysql:mysql /data/mysql/

创建配置文件my.cnf

vim /home/mysql/etc/my.cnf

[mysql]
#CLIENT #
port                           = 3306
socket                         = /data/mysql/data/mysql.sock

[mysqld]
#thread_pool_stall_limit=100
# GENERAL #
user                                      = mysql
port                                      = 3306
default_storage_engine          = InnoDB
basedir                                   = /usr/local/mysql
socket                                    = /data/mysql/data/mysql.sock
pid_file                                  = /data/mysql/data/mysql.pid
character_set_server                      = utf8mb4
skip_name_resolve                         = ON              
lower_case_table_names                    = 1
back_log                                  = 50
read_buffer_size                          = 1M
read_rnd_buffer_size=17825792
sort_buffer_size                          = 16M
join_buffer_size                          = 16M
explicit_defaults_for_timestamp           = TRUE
server_id = 183750931
ignore_db_dir                             = lost+found
default_time_zone                         = '+8:00'
core_file
local_infile                              = 1
log_timestamps                            = system
thread_stack                              = 512K
#log_warnings                              = 2
performance_schema                        = 0      
# MyISAM #
key_buffer_size                           = 8M
myisam_recover_options                    = FORCE,BACKUP
keep_files_on_create                      = ON
# SAFETY #
max_allowed_packet                        = 64M
max_connect_errors                        = 300
sql_mode                                  = ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
sysdate_is_now                            = 1
#innodb                                   = FORCE
innodb_strict_mode                        = 1
# DATA STORAGE #           
datadir                                   = /data/mysql/data/
# BINARY LOGGING #           
log_bin                                   = /data/mysql/binlog/mysql-bin
binlog_format                             = row
expire_logs_days                          = 14
sync_binlog                               = 1
binlog_cache_size                         = 1M
max_binlog_cache_size                     = 2G
# CACHES AND LIMITS #
tmp_table_size                            = 32M
max_heap_table_size                       = 32M
query_cache_type                          = 0
query_cache_size                          = 0
max_connections=1000
#max_connections=1000
thread_cache_size                         = 50
open_files_limit                          = 65535
table_definition_cache                    = 1024
table_open_cache                          = 4096
secure_file_priv=''
# INNODB #           
innodb_flush_method                       = O_DIRECT
innodb_log_files_in_group                 = 2
innodb_log_file_size                      = 1G
innodb_flush_log_at_trx_commit            = 1 
innodb_file_per_table                     = 1
innodb_buffer_pool_size = 2G
innodb_buffer_pool_instances              = 8
transaction_isolation                     = READ-COMMITTED
innodb_data_file_path                     = ibdata1:512M;ibdata2:512M:autoextend
innodb_write_io_threads                   = 8
innodb_read_io_threads                    = 8
innodb_thread_concurrency                 = 32
innodb_max_dirty_pages_pct                = 75
innodb_lock_wait_timeout                  = 120
#innodb_rollback_on_timeout                = 1
innodb_io_capacity                        = 2000
innodb_io_capacity_max                    = 6000
innodb_lru_scan_depth                     = 2000
innodb_flush_neighbors                    = 0
innodb_print_all_deadlocks                = 1
innodb_purge_threads                      = 4
#innodb_undo_tablespaces                   = 3 
innodb_sort_buffer_size                   = 16M
innodb_online_alter_log_max_size          = 1G
innodb_disable_sort_file_cache            = ON
innodb_sync_array_size                    = 16
innodb_page_cleaners                      = 8
internal_tmp_disk_storage_engine          = INNODB
innodb_checksum_algorithm                 = crc32
#innodb_file_format                        = Barracuda
#innodb_file_format_max                    = Barracuda
innodb_stats_on_metadata                  = 0
innodb_sync_spin_loops                    = 100
innodb_spin_wait_delay                    = 30
# LOGGING #
log_error_verbosity                       = 2
log_error                                 = /data/mysql/data/mysql_error.log
log_queries_not_using_indexes             = 1
log_throttle_queries_not_using_indexes    = 20
min_examined_row_limit                    = 100
log_slow_admin_statements                 = 1
log_slow_slave_statements                 = 1
slow_query_log                            = 1
long_query_time                           = 0.5
slow_query_log_file                       = /data/mysql/data/mysql_slow.log
#relay_log                                = /data/mysql/log/relay_log/mysql-relay-bin
#general_log_file                         = /data/mysql/log/general.log
innodb_undo_log_truncate                  = 1
innodb_max_undo_log_size                  = 4G
# REPLICATION
#plugin-load                               = #"rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
log_slave_updates=1
#gtid_mode                                 = ON
gtid_mode                                 = OFF
#rpl_semi_sync_master_enabled = 0
#rpl_semi_sync_master_timeout = 25920000000
#rpl_semi_sync_slave_enabled = 1
#rpl_semi_sync_master_wait_no_slave = 1
#rpl_semi_sync_master_wait_for_slave_count = 1
#rpl_semi_sync_master_wait_point = AFTER_SYNC
#super_read_only=1
enforce_gtid_consistency                  = ON 
binlog_gtid_simple_recovery               = 1
master_info_repository                    = TABLE
relay_log_info_repository                 = TABLE
relay_log_recovery                        = ON
slave_net_timeout                         = 60
innodb_buffer_pool_dump_at_shutdown       = ON
innodb_buffer_pool_load_at_startup        = ON
innodb_buffer_pool_dump_pct               = 50
slave_exec_mode                           = STRICT
slave_rows_search_algorithms              = 'TABLE_SCAN,INDEX_SCAN'
slave_parallel_type                       = LOGICAL_CLOCK
slave_parallel_workers                    = 16
slave_preserve_commit_order               = 1
slave_transaction_retries                 = 128
#slave_compressed_protocol                 = 0
#THREAD_POOL
#thread_handling                           = pool-of-threads
#thread_pool_oversubscribe                 = 10
#thread_pool_size = 36

初始化

/usr/local/mysql/bin/mysqld --defaults-file=/home/mysql/etc/my.cnf --basedir=/usr/local/mysql --datadir=/data/mysql/data/ --user=mysql --initialize

获取初始密码

cat /mysql/data/mysql_error.log |grep 'password'
2022-04-22T07:25:36.535421-08:00 1 [Note] A temporary password is generated for root@localhost: xtq3vbrbux#T

查看当前有无mysql的进程正在运行

ps -ef | grep mysqld

开启mysql实例

#开启实例
mysqld_safe --defaults-file=/home/mysql/etc/my.cnf &
#关闭实例
mysqladmin -uroot -p -S /data/mysql/data/mysql.sock shutdown

使用临时密码登录数据库

启动之后data目录下面才会出现mysql.sock文件，这时候就能用账号localhost和临时密码通过sock文件来登录
mysql -uroot -hlocalhost -p'上面获取到的初始密码'  -S /data/mysql/data/mysql.sock

修改数据库密码

mysql>	alter user user() identified by '123456';
如果直接操作数据库会出现下面错误
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

查看myql.user表的情况，确认新的MySQL用户添加成功

select host ,user,authentication_string from mysql.user;

给root所有权限

mysql>	grant all privileges on *.* to 'root'@'127.0.0.1' identified by '123456' with grant option;
mysql> 	flush privileges;刷新

查看用户权限表mysql.user

select  host, user, authentication_string  from mysql.user;

退出之后就可以使用新的账号密码来登陆了

exit
mysql- u root -h 127.0.0.1 -p
123456

mysql常用工具或命令介绍

mysql常用管理工具：

mysql应该是最常用的一个数据库客户端工具，通常使用数据库前都需要通过msyql -uroot -p来连接数据库。

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

mysqlbinlog用于管理Mysql服务器生成的二进制日志文件

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

mysqlimport 是客户端数据导入工具，用来导入mysqldump 加 -T 参数后导出的文本文件。如果想要导入sql文件，可以使用source指令source xxx.sql

mysqlshow 客户端对象查找工具，用来很快地查找存在哪些数据库、数据库中的表、表中的列或者索引。

myisampack用于对MyIsam存储引擎中的表进行压缩，压缩后的表只能查询，不能执行插入和更新操作。

mysqlcheck可以用于检查和修复MyIsam表，还能优化和分析表。

mysql常用命令：

select @@version;	查询当前mysql的版本.
show variables like ‘port’;	查看mysql实例的端口
show variables like ‘socket’;	查看实例的socket数据
show variables like ‘datadir’;	查看实例的数据路径
show databases;	显示所有数据库名的命令
desc tablename;	显示表结构和列结构的命令
show processlist \G;	显示正在执行的线程
explain	查看语句的执行计划
show index from table_name	查看表的索引情况
select * from STATISTICS where table_name=‘XXX’\G	查看表的统计信息
select @@max_allowed_packet;	查询定义的packet大小
show master status	查看master状态
show master logs	查看所有的log文件，在主服务器上执行
show warnings;	显示最近的警告详情
show variables \G;	查看当前mysqld的所有参数，包括默认值

备份：

mysqldump：mysqldump 是一种用于逻辑备份的客户端工具，它会产生一套能够重新构建数据库或表的SQL语句。所谓逻辑备份：是利用SQL语言从数据库中抽取数据并存于二进制文件的过程。逻辑备份文件只能用来对数据库进行逻辑恢复，即数据导入，而不能按数据库原来的存储特征进行物理恢复

innobackupex：xtrabackup是percona团队研发的备份工具，比MySQL官方的ibbackup的功能还要多。支持myisam温全备、innodb热全备和温增备，还可以实现innodb的定时点恢复，而且备份和恢复的速度都较快。在目前MySQL的备份实现上，考虑价格、速度、安全、一致性等角度，xtrabackup是非常合适的工具。

使用mysqldump工具来实现数据库的复制

复制基本介绍

replication角色 主机IP(根据实际情况) MySQL版本

master 192.168.100.20 MYSQL5.7.19

slave 192.168.100.150 MYSQL5.7.19

不开启GTID的情况下（在创建配置文件my.cnf中注释）

配置master和slave的配置文件,在两者的配置文件中增加或是修改如下内容

[mysqld] # master
datadir=/data/mysql/data
socket=/data/mysql/data/mysql.sock
log-bin=master-bin
sync-binlog=1
server-id=100
#----------------------------------
[mysqld] # slave
datadir=/data/mysql/data
socket=/data/mysql/data/mysql.sock
relay-log=slave-bin
server-id=111
#------------------------------------

重启master和slave的MYSQL实例

ps -ef |grep mysqld   查看进程
这里ip和端口号以及密码均只做参考，均开启各自的数据库实例
#开启实例
mysqld_safe --defaults-file=/home/mysql/etc/my.cnf &
#关闭实例
mysqladmin -uroot -p -S /data/mysql/data/mysql.sock shutdown

在master上创建复制专用的用户 看情况决定参数

create user 'repl'@'192.168.100.%' identified by 'P@ssword1!';
grant REPLICATION SLAVE on *.* to 'repl'@'192.168.100.%';
flush privileges;

导出master中所有数据库的数据

 #--master-data值为2会注释change master，值为1或者没有提供值时，这些语句是直接激活的。同时，--master-data会锁定所有表(如果同时使用了--single-transaction，则不是锁所有表
 mysqldump -uroot -p --all-databases --master-data=2 >master.sql

从master.sql中获取到binlog的坐标

 grep -i -m 1 'change master to' master.sql

在从库执行导出的master.sql

mysql -uroot -p -h 127.1 -e 'source master.sql'

从库连接master

ysql> change master to
master_host='192.168.43.42',
master_port=3306,
master_user='repl',
master_password='P@ssword1!',
master_log_file='master-bin.000002',
master_log_pos=773;

启动IO线程和SQL线程

#一次性启动
start slave;
#分开执行
start slave io_thread;
start slave sql_thread;

在从库查看同步信息

mysql> show slave status\G
Master_Log_File：IO线程正在读取的master binlog
Read_Master_Log_Pos：IO线程已经读取到master binlog的哪个位置
Relay_Log_File：SQL线程正在读取和执行的relay log
Relay_Log_Pos：SQL线程已经读取和执行到relay log的哪个位置
Relay_Master_Log_File：SQL线程最近执行的操作对应的是哪个master binlog
Exec_Master_Log_Pos：SQL线程最近执行的操作对应的是master binlog的哪个位置

reset master与reset slave

reset slave会删除master.info/relay-log.info和relay log，然后新生成一个relay log。但是change master to设置的连接 参数还在内存中保留着，所以此时可以直接start slave，并根据内存中的change master to连接参数复制日志。

reset slave all除了删除reset slave删除的东西，还删除内存中的change master to设置的连接信息

reset master会删除master上所有的二进制日志，并新建一个日志。在正常运行的主从复制环境中，执行reset master很可 能导致异常状况。所以建议使用purge来删除某个时间点之前的日志(应该保证只删除那些已经复制完成的日志)，生产环境慎用
Log_Pos：SQL线程最近执行的操作对应的是master binlog的哪个位置