之前参加MySQL培训后一直都没好好整理一下，现在准备在blog中做一些笔记和总结，主要为了以后在工作中可以方便查阅先 关的知识点
1.机器准备
mkdir /u01
chmod -R 775 /u01/
groupadd -g 510 dba
useradd -u 509 -d /home/mysql -g dba mysql
chown -R mysql.dba /u01
yum -y install gcc gcc-c++ gcc-g77 kernel-devel autoconf automake make cmake perl ncurses-devel libmcrypt* libtool-ltdl-devel* readline-devel pcre-devel openssl-devel openssl
2. 拷贝文件
–
拷贝文件 boost_1_59.tar.gz和 mysql-5.6.26.tar.gz 到/u01目录
执行解压:
安装文件在这
cd /u01
tar -zxvf boost_1_59_0.tar.gz
tar -zxvf mysql-5.6.26.tar.gz
3.目录准备
–
如果数据盘在/data
以下命令在/data下做:
cd /data
mkdir -p my3306/{data,log,run,tmp}
mkdir -p my3307/{data,log,run,tmp}
mkdir -p my3308/{data,log,run,tmp}
mkdir backup
chmod -R 775 my3306/
chmod -R 775 my3307/
chmod -R 775 my3308/
chmod -R 775 backup/
chown -R mysql.dba my3306/
chown -R mysql.dba my3307/
chown -R mysql.dba my3308/
chown -R mysql.dba backup/
生成软链接
如果没有/data 就在/u01下做。并且不用生成软连接
ln -s /data/my3306/ /u01/my3306
ln -s /data/my3307/ /u01/my3307
ln -s /data/my3308/ /u01/my3308
4.安装MySQL软件
–
mv boost_1_59_0/ boost
mkdir /u01/mysql
chmod -R 775 /u01/
chown -R mysql.dba /u01/
cd /u01/mysql-5.6.26/
进行编译和安装：
cmake -DCMAKE_INSTALL_PREFIX=/u01/mysql -DMYSQL_DATADIR=/u01/my3306 -DSYSCONFDIR=/etc -DWITH_MYISAM_STORAGE_ENGINE=1 -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_MEMORY_STORAGE_ENGINE=1 -DWITH_READLINE=1 -DMYSQL_UNIX_ADDR=/tmp/mysqld.sock -DMYSQL_TCP_PORT=3306 -DENABLED_LOCAL_INFILE=1 -DWITH_PARTITION_STORAGE_ENGINE=1 -DEXTRA_CHARSETS=all -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DDOWNLOAD_BOOST=1 -DWITH_BOOST=/u01/boost
make -j 4 && make install -j 4
到这里 软件软件已经安装完成
5.安装MySQL实例
cd /u01/my3306/
vi my.cnf
my.cnf 配置的内容见附录
/u01/mysql/scripts/mysql_install_db --defaults-file=/u01/my3306/my.cnf --datadir=/u01/my3306/data/ --basedir=/u01/mysql/ --user=mysql
切换用户
su - mysql
以下操作使用MySQL用户
cd
vi .bash_profile
bash_profile 内容也在最后 注意运维脚本放到 mysql/local/bin中，这个在bash中也有配bin的目录，关注下。 记得改脚本的密码
source .bash_profile
起动数据库
mysqld_mm
切换到库
my3306
mysql -uroot -p 这个时候没有密码 进去执行下面的语句 这个时密码就是root@1234了
## 这个报错ALTER USER 'root'@'localhost' identified by 'root@1234';
## 用这个语句改
use mysql
UPDATE user SET Password = PASSWORD('root@1234') WHERE user = 'root';
FLUSH PRIVILEGES;
create user dbadmin@'localhost' identified by 'root@1234';
create user test@'%' identified by 'Pass1234';
grant all privileges on *.* to 'test'@'%';
grant all privileges on *.* to 'dbadmin'@'localhost';
-- 如果5.7版本
UPDATE mysql.user SET authentication_string=PASSWORD('root@1234'),password_expired='N' WHERE user='root';
-- 如果5.6版本
UPDATE mysql.user SET Password=PASSWORD('root@1234'),password_expired='N' WHERE user='root';
flush privileges;
GRANT REPLICATION SLAVE ON *.* to 'slave'@'%' identified by 'slave';
flush privileges;
UPDATE mysql.user SET Password=PASSWORD('Pass1234'),password_expired='N' WHERE user='test';
create database xxx;
改账号报错有时候修改为不是localhost 为 % 可以解决 应该和机器有关
6.配置附录
my.cnf:
[mysqld_safe]
pid-file=/u01/my3306/run/mysqld.pid
# malloc-lib=/u01/mysql/lib/libjemalloc.so
[mysql]
port=3306
prompt=\\u@\\d \\r:\\m:\\s>
default-character-set=utf8mb4
no-auto-rehash
#skip-insecure-warning
[client]
default-character-set=utf8mb4
port=3306
socket=/u01/my3306/run/mysql.sock
user=dbadmin
password=alipswxx
[mysqld]
#dir
basedir=/u01/my3306
datadir=/u01/my3306/data
tmpdir=/u01/my3306/tmp
lc_messages_dir=/u01/mysql/share
log-error=/u01/my3306/log/alert.log
slow_query_log_file=/u01/my3306/log/slow.log
general_log_file=/u01/my3306/log/general.log
socket=/u01/my3306/run/mysql.sock
# close 5.6 variables for 5.5
binlog_checksum=none
log_bin_use_v1_row_events=on
# log_bin_use_old_datetime_format=on
explicit_defaults_for_timestamp=true
binlog_row_image=FULL
binlog_rows_query_log_events=OFF
binlog_stmt_cache_size=32768
#innodb
innodb_data_home_dir=/u01/my3306/data
innodb_log_group_home_dir=/u01/my3306/data
#innodb_data_file_path = ibdata1:4G;ibdata2:16M:autoextend
innodb_data_file_path = ibdata1:1G;ibdata2:200M:autoextend
innodb_buffer_pool_instances=4
innodb_log_files_in_group=4
innodb_log_file_size=1G
innodb_log_buffer_size=200M
innodb_flush_log_at_trx_commit=1
#innodb_additional_mem_pool_size=20M #deprecated in 5.6
innodb_max_dirty_pages_pct=60
innodb_io_capacity_max=6000
innodb_io_capacity=1000
innodb_thread_concurrency=48
innodb_read_io_threads=8
innodb_write_io_threads=8
innodb_open_files=60000
innodb_file_format=Barracuda
innodb_file_per_table=1
innodb_flush_method=O_DIRECT
innodb_change_buffering=inserts
innodb_adaptive_flushing=1
#innodb_adaptive_flushing_method=keep_average #percona
#innodb_adaptive_hash_index_partitions=1 #percona
#innodb_fast_checksum=1 #percona
#innodb_lazy_drop_table=0 #percona
innodb_old_blocks_time=1000
innodb_stats_on_metadata=0
# innodb_read_ahead=0
innodb-read-ahead-threshold=0
innodb_use_native_aio=1
innodb_lock_wait_timeout=5
innodb_rollback_on_timeout=0
innodb_purge_threads=4
innodb_strict_mode=1
transaction-isolation=READ-COMMITTED
innodb_disable_sort_file_cache=ON
innodb_lru_scan_depth=2048
innodb_flush_neighbors=0
innodb_sync_array_size=16
innodb_print_all_deadlocks
innodb_checksum_algorithm=CRC32
innodb_max_dirty_pages_pct_lwm=10
innodb_buffer_pool_size=8G
#myisam
key_buffer_size=128M
myisam_sort_buffer_size=128M
concurrent_insert=2
delayed_insert_timeout=300
#replication
master-info-file=/u01/my3306/log/master.info
relay-log=/u01/my3306/log/relaylog
relay_log_info_file=/u01/my3306/log/relay-log.info
relay-log-index=/u01/my3306/log/mysqld-relay-bin.index
slave_load_tmpdir=/u01/my3306/tmp
slave_type_conversions="ALL_NON_LOSSY"
slave_net_timeout=4
skip-slave-start
sync_master_info=1000
sync_relay_log_info=1000
master_info_repository=TABLE
relay_log_info_repository=TABLE
report_host=10.18.19.43
report_port=3306
slave-parallel-workers=4
relay_log_recovery=ON
#binlog
log-bin=/u01/my3306/log/mysql-bin
server_id=433306
binlog_cache_size=32K
max_binlog_cache_size=2G
max_binlog_size=500M
binlog-format=ROW
sync_binlog=100
log-slave-updates=1
expire_logs_days=3
rpl_stop_slave_timeout=300
slave_checkpoint_group=1024
slave_checkpoint_period=300
slave_parallel_workers=4
# slave_pr_mode=TABLE
slave_pending_jobs_size_max=1073741824
slave_rows_search_algorithms='TABLE_SCAN,INDEX_SCAN'
slave_sql_verify_checksum=OFF
master_verify_checksum=OFF
gtid_mode=ON
enforce_gtid_consistency=ON
#server
default-storage-engine=INNODB
character-set-server=utf8mb4
lower_case_table_names=1
skip-external-locking
open_files_limit=65535
safe-user-create
local-infile=1
#sqlmod="STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE"
performance_schema=0
log_slow_admin_statements=1
#log_slow_verbosity=full
log_warnings=1
long_query_time=5
slow_query_log=1
general_log=0
query_cache_type=0
query_cache_limit=1M
query_cache_min_res_unit=1K
table_definition_cache=65536
metadata_locks_hash_instances=256
metadata_locks_cache_size=32768
eq_range_index_dive_limit=200
table_open_cache_instances=16
thread_stack=512K
thread_cache_size=256
read_rnd_buffer_size=128K
sort_buffer_size=256K
join_buffer_size=128K
read_buffer_size=128K
port=3306
skip-name-resolve
skip-ssl
max_connections=8500
max_user_connections=8000
max_connect_errors=65536
max_allowed_packet=128M
connect_timeout=8
net_read_timeout=30
net_write_timeout=60
back_log=1024
slave_exec_mode=IDEMPOTENT
# thread_pool_size=32
# thread_pool_stall_limit=30
# thread_handling=pool-of-threads
bash_profile:
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/bin
export PATH
# User specific environment and startup programs
export MYSQL_HOME=/u01/my3306
export PATH=$PATH:$HOME/bin:/u01/mysql/bin:/u01/mysql/local/bin
export UMASK=0600
export UMASK_DIR=0700
unset USERNAME
export LANG=en_US.UTF-8
alias alert="tail -100f $MYSQL_HOME/log/alert.log"
alias rm="/bin/rm -i --"
alias dump="cd $MYSQL_HOME/log"
#export LC_CTYPE = UTF-8
alias a-alert='tail -80f /u01/my3306/log/alert.log'
alias a-check-hardware='sudo /usr/alisys/dragoon/libexec/monitor/hardware/get_server_hardware.sh'
alias a-checkssd='sudo /opt/satools/check_ssd.py'
alias a-datadir='cd /u01/my3306/data'
alias a-dbasql='dbasql -P3306 '
alias a-dbs='dbs'
alias a-hwconfig='sudo /opt/satools/hwconfig'
alias a-logdir='cd /u01/my3306/log'
alias a-mysqladmin='a-mysqladmin -P3306 '
alias a-mysqldump='a-mysqldump -P3306 '
alias a-orzdba='/usr/local/bin/orzdba'
alias a-orztop='/usr/local/bin/orztop'
alias a-raid='sudo tbraid'
alias a-relayfetch='/usr/local/bin/relayfetch'
alias a-relayfetch55='/usr/local/bin/relayfetch55'
alias a-sql='rdsql -P3306 '
alias a-tbsql='/usr/local/bin/tbsql'
alias check_hardware='sudo /usr/alisys/dragoon/libexec/monitor/hardware/get_server_hardware.sh'
alias chgrp='chgrp --preserve-root'
alias chmod='chmod --preserve-root'
alias chown='chown --preserve-root'
alias dbasql='dbasql -P3306 '
alias l.='ls -d .* --color=auto'
alias ll='ls -l --color=auto'
alias ls='ls --color=auto'
alias my1='export MYSQL_HOME=/u01/my3306 && source dbrole.sh'
alias my2='export MYSQL_HOME=/u01/my3406 && source dbrole.sh'
alias my3='export MYSQL_HOME=/u01/my3506 && source dbrole.sh'
alias my4='export MYSQL_HOME=/u01/my3606 && source dbrole.sh'
alias my3306='export MYSQL_HOME=/u01/my3306 && source dbrole.sh'
alias my3307='export MYSQL_HOME=/u01/my3307 && source dbrole.sh'
alias my3308='export MYSQL_HOME=/u01/my3308 && source dbrole.sh'
alias my3309='export MYSQL_HOME=/u01/my3309 && source dbrole.sh'
alias mysqld_mm='mysqld_safe --defaults-file=$MYSQL_HOME/my.cnf &'
alias mysqld_ms='mysqld_safe --defaults-file=$MYSQL_HOME/my.cnf --read-only=1 &'
alias mysqld_readonly='mysqld_safe --read-only=1 &'
alias mysqld_writable='mysqld_safe &'
alias rm='/bin/rm -i --'
alias vi='vim'
alias which='alias | /usr/bin/which --tty-only --read-alias --show-dot --show-tilde'
PORTS=`ps -elf | grep mysqld | perl -wn -e 'BEGIN{my @arr;}{ m/datadir=(\S+) .*log-error=(\S+) .*socket=(\S+) .*port=(\S+)/ and push @arr,$4;}END{my @sa = sort @arr; print "@sa"; }'`
NUM_INST=`echo $PORTS | tr ' ' '\n' | wc -l`
if [ "$PORTS" = "" ]
then
echo No MySQL running, Please Check!
export PS1="\n\e[1;37m[\e[m\e[1;34mNoMySQL\e[m\e[1;35m@\e[m\e[1;32m\H\e[m \w\e[m\e[1;37m]\e[m\e[1;36m\e[m\n\$"
else
echo "There are $NUM_INST MySQL instance(s) running"
echo "[ $PORTS ]"
export PS1="\n\e[1;37m[\e[m\e[1;34m$NUM_INST-MySQL-Inst\e[m\e[1;35m@\e[m\e[1;32m\H\e[m \w\e[m\e[1;37m]\e[m\e[1;36m\e[m\n\$"
fi
其中有一些DBA的运维脚本，我也会上传的到install-file的项目中
