从今天开始尽量每天保持更新学习MySQL系列的相关博客,记录下自己整个学习过程的同时,也希望能对于未来的想入门MySQL运维方向的童鞋有点帮助。
关于学习数据库,先不管三七二十一,咱先得有个环境不是,第一篇文章先写写mysql数据库在linux上的搭建过程;
对于搭建过程其实不需要问太多的为什么,很多东西等你入门之后回头再看自然就明白了。
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
首先你需要到mysql的官网下载对应的安装包,下载地址为:https://dev.mysql.com/downloads/mysql/
进去之后操作系统选择 Linux - Generic,因为指定操作系统类型只提供相应的rpm安装包,这里讲的是比较简单又不失灵活性的二进制安装方式。
以下为安装过程:
1、安装MySQL服务依赖的软件包
yum install -y perl-Time-HiRes make gcc-c++ bison ncurses-devel libaio time numactl-devel
2、创建mysql用户
groupadd mysql
useradd -g mysql -s /sbin/nologin mysql
passwd mysql
3、将官网下载的安装包上传解压并放到/usr目录下
tar -zxvf mysql-5.7.16-linux-glibc2.5-x86_64.tar.gz
mv ./mysql-5.7.16-linux-glibc2.5-x86_64 /usr/local/
ln -s /usr/local/mysql-5.7.16-linux-glibc2.5-x86_64 /usr/local/mysql
4、创建数据、日志存放路径
mkdir -p /opt/mysql/log
mkdir -p /opt/mysql/data
mkdir -p /opt/mysql/tmp
chown -R mysql:mysql /opt/mysql
chown -R mysql:mysql /usr/local/mysql-5.7.16-linux-glibc2.5-x86_64
5、配置数据库命令搜寻路径
echo "export PATH=/usr/local/mysql/bin:$PATH" >> /etc/profile
ource /etc/profile
6、修改mysql配置文件
mv /etc/my.cnf /etc/my.cnf.bak
vi /etc/my.cnf#将以下内容填入进去,每个参数的作用以后慢慢理解
[mysqld]
# basic settings #
user = mysql
sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER"
basedir = /usr/local/mysql
datadir = /opt/mysql/data
socket = /tmp/mysql.sock
pid_file = /opt/mysql/mysql.pid
port = 3306
log_bin = /opt/mysql/log/bin.log
log_bin_index = /opt/mysql/log/bin.index
server-id = 04606
report_host=10.248.2.46
report-port=3306
autocommit = 1
character_set_server=utf8mb4
skip_name_resolve = 1
max_connections = 1500
max_connect_errors = 1000000
transaction_isolation = READ-COMMITTED
explicit_defaults_for_timestamp = 1
tmpdir = /opt/mysql/tmp/
max_allowed_packet = 16777216
event_scheduler = 1
# connection #
interactive_timeout = 1800
wait_timeout = 1800
lock_wait_timeout = 10
# table cache performance settings
table_open_cache = 4096
table_definition_cache = 4096
table_open_cache_instances = 128
# session memory settings #
read_buffer_size = 16M
read_rnd_buffer_size = 32M
sort_buffer_size = 32M
lower_case_table_names = 1
tmp_table_size = 64M
join_buffer_size = 128M
thread_cache_size = 64
slave_pending_jobs_size_max=67108864
# 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 = 15
long_query_time = 2
min_examined_row_limit = 10000
binlog-rows-query-log-events = 1
log-bin-trust-function-creators = 1
log-slave-updates = 1
# innodb settings #
innodb_page_size = 16384
innodb_buffer_pool_size = 512M
innodb_buffer_pool_instances = 2
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_lru_scan_depth = 128
innodb_lock_wait_timeout = 5
innodb_io_capacity = 1024
innodb_io_capacity_max = 2048
innodb_flush_method = O_DIRECT
innodb_file_format = Barracuda
innodb_file_format_max = Barracuda
innodb_undo_logs = 128
innodb_undo_tablespaces = 3
innodb_flush_neighbors = 0
#innodb_log_file_size = 17179869184
#innodb_log_files_in_group = 2
#innodb_log_buffer_size = 16777216
#innodb_max_undo_log_size = 2147483648
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
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_file_per_table = 1
innodb_stats_persistent_sample_pages = 64
innodb_autoinc_lock_mode = 2
#innodb_online_alter_log_max_size = 1G
#innodb_open_files=4096
innodb_flush_neighbors = 1
# replication settings #
master_info_repository = TABLE
relay_log_info_repository = TABLE
sync_binlog = 1
gtid_mode = on
enforce_gtid_consistency = 1
log_slave_updates
binlog_format = ROW
binlog_rows_query_log_events = 1
relay_log = relay.log
relay_log_recovery = 1
slave_skip_errors = ddl_exist_errors
slave-rows-search-algorithms = 'INDEX_SCAN,HASH_SCAN'
# semi sync replication settings #
#plugin_load = "validate_password.so;rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
rpl_semi_sync_master_enabled = 1
rpl_semi_sync_master_timeout = 3000
rpl_semi_sync_slave_enabled = 1
# password plugin #
#validate_password_policy=STRONG
#validate-password=FORCE_PLUS_PERMANENT
[mysqld-5.6]
# metalock performance settings
metadata_locks_hash_instances=64
[mysqld-5.7]
# new innodb settings #
#loose_innodb_numa_interleave=1
innodb_buffer_pool_dump_pct = 40
innodb_page_cleaners = 16
innodb_undo_log_truncate = 1
innodb_max_undo_log_size = 2G
innodb_purge_rseg_truncate_frequency = 128
# new replication settings #
slave-parallel-type = LOGICAL_CLOCK
slave-parallel-workers = 16
slave_preserve_commit_order=1
slave_transaction_retries=128
# other change settings #
binlog_gtid_simple_recovery=1
log_timestamps=system
show_compatibility_56=on
[mysql]
prompt = [\\u@\\h][\\d]>\\_
#####################################################################################################################
chmod 644 /etc/my.cnf
7、初始化实例配置
/usr/local/mysql/bin/mysqld --initialize --user=mysql --datadir=/opt/mysql/data
8、添加mysql服务至系统服务
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
9、启动mysql实例
service mysqld start
报错:Starting MySQL.. ERROR! The server quit without updating PID file (/opt/mysql/data/ppcdb.pid).
遇到报错不要慌,先看错误日志:
cat /opt/mysql/data/error.log | grep error
[ERROR] InnoDB: Expected to open 3 undo tablespaces but was able to find only 0 undo tablespaces. Set the innodb_undo_tablespaces parameter to the correct value and retry. Suggested value is 0#说是undo tablespace应该有三个,只找到0个
查看数据目录确实没有undo表空间的文件
[root@ppcdb data]# ll
total 110636
-rw-r----- 1 mysql mysql 56 Aug 11 11:05 auto.cnf
-rw-r----- 1 mysql root 3062 Aug 11 11:08 error.log
-rw-r----- 1 mysql mysql 359 Aug 11 11:08 ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 Aug 11 11:08 ibdata1
-rw-r----- 1 mysql mysql 50331648 Aug 11 11:08 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 Aug 11 11:05 ib_logfile1
drwxr-x--- 2 mysql mysql 4096 Aug 11 11:05 mysql
drwxr-x--- 2 mysql mysql 4096 Aug 11 11:05 performance_schema
-rw-r----- 1 mysql root 8367 Aug 11 11:08 ppcdb.err
drwxr-x--- 2 mysql mysql 12288 Aug 11 11:05 sys
联想到由于在初始化之后我又修改过my.cnf文件,undo文件未能初始化出来,所以启动失败了,这里重新初始化解决
rm -rf /opt/mysql/data/*
rm -rf /opt/mysql/log/*
/usr/local/mysql/bin/mysqld --initialize --user=mysql --datadir=/opt/mysql/data
service mysqld start#启动成功
10、首次登录需要密码,初始密码在error.log中记录着
cat /opt/mysql/data/error.log | grep "A temporary password is generated for root@localhost"
11、修改登录密码,不修改的话做任何操作都会报错
mysqladmin -u root -p'j?y.u#4c9;mK' password 111111 -S /tmp/mysql.sock
这样数据库就安装完成,后面可以用新的密码开始操作数据库了.