Mysql之CentOS安装
1. Mysql的安装方式
1. yum安装mysql
适合对数据库要求不太高的场合，例如：并发不大，公司内部，企业内部。
1. 官网下载yum源，wget https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
2. rpm -ivh rpm包文件
3. 安装这个包后，会获得两个mysql的yum repo源：/etc/yum.repos.d/mysql-community.repo，/etc/yum.repos.d/mysql-community-source.repo。
4. yum install mysql-server -y
5. 启动systemctl start mysqld.service
6. 登录mysql -uroot -p
2. 二进制方式安装mysql
很多专业DBA的选择
1. 下载指定版本的二进制文件包。
cd /home/tools
wget https://cdn.mysql.com//Downloads/MySQL-5.6/mysql-5.6.40-linux-glibc2.12-x86_64.tar.gz
2. 解压压缩包
tar -zxvf mysql-5.6.40-linux-glibc2.12-x86_64.tar.gz
3. 将文件包移动到/application下，并且创建一个软链接到/usr/local/mysql
mv mysql-5.6.40-linux-glibc2.12-x86_64 /application
ln -s /application/mysql-5.6.40-linux-glibc2.12-x86_64/ /application/mysql
4. 创建一个mysql用户和组
useradd -s /sbin/nologin -M mysql
5. 将/application/mysql/下的所有目录属主和属组都设置为mysql。
chown -R mysql.mysql /application/mysql/
6. 初始化mysql
scripts/mysql_install_db --user=mysql
报错：
FATAL ERROR: please install the following Perl modules before executing scripts/mysql_install_db:
Data::Dumper
解决方法：
yum install autoconf -y
再次执行初始化，没有这个报错了。又有其他报错。
WARNING: Found existing config file ./my.cnf on the system.
Because this file might be in use, it was not replaced,
把/etc/my.cnf文件删掉，再执行初始化。
然后检查data目录下，是不是有下面的文件。
-rw-rw---- 1 mysql mysql 12582912 6月 17 01:21 ibdata1
-rw-rw---- 1 mysql mysql 50331648 6月 17 01:21 ib_logfile0
-rw-rw---- 1 mysql mysql 50331648 6月 17 01:18 ib_logfile1
drwx------ 2 mysql mysql 4096 6月 17 01:18 mysql
drwx------ 2 mysql mysql 4096 6月 17 01:21 performance_schema
drwxr-xr-x 2 mysql mysql 19 6月 15 01:40 test
也可以通过查看data/机器名.err
其它错误
错误: Installing MySQL system tables..../bin/mysqld: error while loading shared libraries: libnuma.so.1: cannot open shared object file: No such file or directory
解决: yum install numactl -y
错误：Installing MySQL system tables..../bin/mysqld: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory
解决：yum install libaio -y
错误：FATAL ERROR: Could not find ./bin/my_print_defaults
解决：回到安装目录进行，执行脚本./scripts/mysql_install_db --user=mysql，其它目录执行，就报这个错误。
7. 把当前目录的用户设置为root。
chown -R root .
8. 把data目录的属主设置为mysql.
chown -R mysql data
drwxr-xr-x 2 root mysql 4096 6月 15 01:40 bin
-rw-r--r-- 1 root mysql 17987 2月 26 20:46 COPYING
drwxr-xr-x 5 mysql mysql 104 6月 17 01:21 data
drwxr-xr-x 2 root mysql 52 6月 15 01:40 docs
drwxr-xr-x 3 root mysql 4096 6月 15 01:39 include
drwxr-xr-x 3 root mysql 4096 6月 15 01:40 lib
drwxr-xr-x 4 root mysql 28 6月 15 01:40 man
-rw-r--r-- 1 root root 943 6月 17 01:21 my.cnf
-rw-r--r-- 1 root root 943 6月 17 01:19 my-new.cnf
drwxr-xr-x 10 root mysql 4096 6月 15 01:40 mysql-test
-rw-r--r-- 1 root mysql 2496 2月 26 20:46 README
drwxr-xr-x 2 root mysql 29 6月 15 01:40 scripts
drwxr-xr-x 28 root mysql 4096 6月 15 01:40 share
drwxr-xr-x 4 root mysql 4096 6月 15 01:39 sql-bench
drwxr-xr-x 2 root mysql 130 6月 15 01:39 support-files
9. cp support-files/mysql.server /etc/init.d/mysqld
10. sed -i 's#/usr/local/mysql#/application/mysql#g' /application/mysql/bin/mysqld_safe /etc/init.d/mysqld
11. /etc/init.d/mysqld start
或者后台启动mysql
bin/mysqld_safe --user=mysql &
[root@rsync-server mysql]# ps -ef | grep mysql | grep -v "grep"
root 5509 1530 0 01:30 pts/2 00:00:00 /bin/sh bin/mysqld_safe --user=mysql
mysql 5590 5509 1 01:30 pts/2 00:00:01 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=rsync-server.err --pid-file=rsync-server.pid
使用mysqld_safe启动的原因：它是mysqld的守护进程，当mysqld被强制关闭，mysqld_safe会自动启动一个新的进程mysqld。
11. 把/application/mysql/bin添加到环境变量
echo "export PATH=/application/mysql/bin:$PATH" >>/etc/profile
source /etc/profile
12. 清理无用的账户及库
drop user "root"@"::1";
drop user ""@"localhost";
drop user ""@"www";
drop user "root"@"www";
flush privileges;
drop database test;
3. 二进制安装mysql5.7版本
1. 下载指定版本
2. 解压
tar -zxvf mysql-5.7.25-linux-glibc2.12-x86_64.tar.gz
3. 移动到指定目录
mv mysql-5.7.25-linux-glibc2.12-x86_64 /usr/local
ln -s mysql-5.7.25-linux-glibc2.12-x86_64 /usr/local/mysql
4. 创建mysql用户
useradd -s /sbin/nologin -M mysql
5. 配置服务
cp support-files/mysql.server /etc/init.d/mysqld
chmod 755 /etc/init.d/mysqld
chown -R mysql.mysql /usr/local/mysql-5.7.25-linux-glibc2.12-x86_64
6. 安装初始化
创建my.cnf文件，并填写指定的配置。
创建对应的目录文件，mkdir -p /server/mysql/{var,tmp,log}
/usr/local/mysql/bin/mysqld --initialize --user=mysql > /root/initialize.log 2>&1
查看error.log是否有报错。
7. 配置环境变量
加入环境变量，编辑 /etc/profile，这样可以在任何地方用mysql命令了
vi /etc/profile
添加mysql路径，加入下面内容，按ESC-->:wq保存
export PATH=$PATH:/usr/local/mysql/bin
刷新立即生效
source /etc/profile
8. 启动mysql服务
/etc/init.d/mysqld start
9. 首次登陆以后，需要修改密码
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
alter user user() identified by "123456"; 修改root的命令，不需要修改任何字符
10. 开机启动mysql
1. 创建启动文件
touch /usr/lib/systemd/system/mysql.service
2. 输入内容
[Unit] Description=MySQL Server Documentation=man:mysqld(8) Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html After=network.target After=syslog.target [Install] WantedBy=multi-user.target [Service] User=mysql Group=mysql ExecStart=/usr/local/mysql/bin/mysqld_safe --user=mysql LimitNOFILE = 5000 #Restart=on-failure #RestartPreventExitStatus=1 #PrivateTmp=false
3. 设置开机启动
systemctl enable mysql
4. 编译安装mysql
普通运维人员采用编译的方式。
编译安装5.6.40版本。
1. 准备工作
yum install ncurses-devel libaio-devel -y
rpm -qa ncurses-devel libaio-devel
2. yum install cmake -y
3. useradd -a /sbin/nologin -M mysql
4. cd /home/tools
wget https://cdn.mysql.com//Downloads/MySQL-5.6/mysql-5.6.40.tar.gz
tar -zxvf mysql-5.6.40.tar.gz
5. cd mysql-5.6.40
cmake . -DCMAKE_INSTALL_PREFIX=/application/mysql-5.6.40 \ -DMYSQL_DATADIR=/application/mysql-5.6.40/data \ -DSYSCONFDIR=/etc \ -DWITH_MYISAM_STORAGE_ENGINE=1 \ -DWITH_INNOBASE_STORAGE_ENGINE=1 \ -DWITH_MEMORY_STORAGE_ENGINE=1 \ -DWITH_READLINE=1 \ -DMYSQL_UNIX_ADDR=/application/mysql-5.6.40/tmp/mysql.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 \ -DMYSQL_USER=mysql \ -DWITH_DEBUG=0 \ -DWITH_SSL=system
6. make 这个时间比较长
7. make install
建议：版本首选5.5以上，安装机器数量少，选择编译；数量多，选择二进制方式。
2. Mysql读取配置文件的顺序
/etc/my.cnf --> /etc/mysql/my.cnf --> /usr/local/mysql/etc/my.cnf --> ~/my.cnf
第一个找/etc/my.cnf，没有找到，寻找下一个，如果都没有，会有默认值
3. ansible安装mysql5.7
1. 在/etc/ansible目录下创建mysq57.yml,内容如下：
- hosts: all ####这个是hosts定义个主机组名字，如果是单台主机可以直接写地址 remote_user: yangjianbo ####执行用户 roles: #####解释配置文件目录 - mysql5.7 become: yes
2. 在/etc/ansible/roles目录下，新建目录mysql5.7，该目录结构如下：
├── defaults
├── files
│ ├── my.cnf
│ └── mysql-5.7.25-linux-glibc2.12-x86_64.tar.gz
├── handlers
├── meta
├── tasks
│ └── main.yml
├── templates
│ └── my.cnf.j2
└── vars
3. 其中main.yml的内容如下：
- name: copy mysql copy: src=mysql-5.7.25-linux-glibc2.12-x86_64.tar.gz dest=/tmp - name: tar mysql unarchive: src=/tmp/mysql-5.7.25-linux-glibc2.12-x86_64.tar.gz dest=/usr/local copy=no - name: create mysql group group: name=mysql system=yes - name: create mysql user user: name=mysql group=mysql shell=/sbin/nologin create_home=no - name: create file file: path=/server state=directory owner=mysql group=mysql recurse=yes - name: create file file: path=/server/mysql/var/data state=directory owner=mysql group=mysql - name: create file file: path=/server/mysql/log state=directory owner=mysql group=mysql - name: create file file: path=/server/mysql/tmp state=directory owner=mysql group=mysql - name: mv dir shell: /bin/mv /usr/local/mysql-5.7.25-linux-glibc2.12-x86_64 /usr/local/mysql - name: copy mysql.server shell: /usr/bin/cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld - name: chmod 755 mysqld file: path=/etc/init.d/mysqld mode=0755 - name: copy my.cnf template: src=/etc/ansible/roles/mysql5.7/templates/my.cnf.j2 dest=/etc/my.cnf - name: init mysql shell: /usr/local/mysql/bin/mysqld --initialize --user=mysql > /tmp/initialize.log 2>&1 - name: environment variable shell: echo "export PATH=$PATH:/usr/local/mysql/bin" >>/etc/profile - name: source /etc/profile shell: source /etc/profile
4. 其中模板my.cnf.j2的内容如下：
[client]
port = 3306
socket = /server/mysql/tmp/mysql.sock
# Here follows entries for some specific programs
# The MySQL server
[mysqld]
port = 3306
datadir = /server/mysql/var/data
pid-file = /server/mysql/var/db.pid
log-error = /server/mysql/log/error.log
socket = /server/mysql/tmp/mysql.sock
user = mysql
tmpdir = /tmp
log_timestamps = SYSTEM
character-set-server = utf8
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
#show_compatibility_56 = on
#skip-grant-tables
skip-external-locking
skip-name-resolve
key_buffer_size = 512M
max_allowed_packet = 512M
table_open_cache = 1024
sort_buffer_size = 512M
binlog_cache_size = 64M
net_buffer_length = 8K
read_buffer_size = 128M
read_rnd_buffer_size = 128M
tmp_table_size = 512M
join_buffer_size = 64M
max_heap_table_size = 128M
bulk_insert_buffer_size = 256M
max_connections = 3000
max_user_connections = 800
max_connect_errors = 10000
#SSD config
innodb_io_capacity = 5000
innodb_flush_method = O_DIRECT
#innodb_support_xa = 1
innodb_read_io_threads = 24
innodb_write_io_threads = 24
#innodb_rollback_on_timeout
innodb_buffer_pool_instances = 8
query_cache_size = 0
query_cache_type = 0
#log-queries-not-using-indexes
long_query_time = 1
slow_query_log = 1
slow_query_log_file = /server/mysql/log/slow.log
#config semi_sync
#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
server-id = {{server_id}}
read_only = off
binlog_format = mixed
log-bin = /server/mysql/log/mysql-bin
log-bin-index = /server/mysql/log/mysql-bin.index
relay-log = /server/mysql/log/relay-log
relay_log_index = /server/mysql/log/relay-log.index
log_slave_updates = 1
relay_log_recovery = 1
#Innodb
innodb_buffer_pool_size = 2G
innodb_file_per_table = 1
innodb_data_home_dir = /server/mysql/var/data
innodb_data_file_path = ibdata1:1G:autoextend
innodb_log_group_home_dir = /server/mysql/log
innodb_log_files_in_group = 2
innodb_log_file_size = 512M
innodb_log_buffer_size = 512M
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1
innodb_lock_wait_timeout = 50
#myisam_max_sort_file_size = 5G
myisam_repair_threads = 1
default-storage-engine = InnoDB
max_binlog_cache_size = 512M
expire_logs_days = 90
group_concat_max_len = 10240
transaction_isolation = REPEATABLE-READ
skip-symbolic-links
skip-slave-start
back_log = 200
open_files_limit = 65535
thread_cache_size = 128
#thread_concurrency = 96
local_infile = 1
innodb_max_dirty_pages_pct = 50
slave-parallel-type = LOGICAL_CLOCK
slave-parallel-workers = 16
#binlog-checksum = CRC32
#binlog-checksum = NONE
master_info_repository = TABLE
relay_log_info_repository = TABLE
relay_log_recovery = ON
log_slave_updates = ON
浙公网安备 33010602011771号