centos6下如何安装mysql服务_CentOS6.5下安装mysql

Mysql5.7版本有较大的变化，特别是在mysql主从上面，可以提供并行复制，所以效率上更高，以后如果有类似需求，需要提供主从复制的话，建议事业部同事选择5.7的版本。

选择安装的包都为源码包，定义起来较为方便，由于全部都为文字节目操作，所以截图部分较少。

一. mysql 安装

1. 基础包安装

a. 首先清理自带的mysql相关的包

i. yum -y remove mysql-*

b. 安装编译mysql需要包

i. yum install gcc gcc-c++ -y

ii. yum install ncurses-devel –y

iii. cmake 安装，版本要求2.8或者3.0以上的

tar xvf cmake*

cd cmake-3.4.3

./configure

make –j 10     ---------------数字为表cpu核数，并行执行，加快编译速度

make install

注意：boost也为mysql编译必须的包，5.7对其版本也有要求，下载mysql包含boost的就无需手动在安装了---------下面步骤可略过

tar xvf boost*

cd boost_1_61_0

./bootstrap.sh –prefix=/usr/local/

./b2

./b2 install

2. mysql环境准备

a. mkdir -pv /data/mysql

b. useradd -s /sbin/nologin mysql

c. chown -R mysql.mysql /data/mysql

3. 编译安装mysql

a. tar xvf mysql-boost-5.7.12.tar.gz

b. cd mysql-5.7.12

c. cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql  -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DWITH_BOOST=/opt/package/boost_1_59_0/        ----------制定mysql安装目录、默认字符集、boost路径

d. make -j 40 && make install

4. mysql初始化

a. 建立mysql配置文件  /etc/my.cnf   -----无论需不需要配置主从，都建议先把server-id以及log-bin配置了，避免以后有添加配置主从的要求

拷贝配置文件模板为新的mysql配置文件

cp /usr/local/mysql/support-files/my-default.cnf /etc/my.cnf

# cat /etc/my.cnf

[client]

port = 3306

socket = /tmp/mysql.sock

default-character-set = utf8mb4

[mysqld]

port = 3306

socket = /tmp/mysql.sock

basedir = /usr/local/mysql

datadir = /data/mysql

pid-file = /data/mysql/mysql.pid

user = mysql

server-id = 29

init-connect = 'SET NAMES utf8mb4'

character-set-server = utf8mb4

skip-name-resolve

#skip-networking

back_log = 300

max_connections = 1000

max_connect_errors = 6000

open_files_limit = 65535

table_open_cache = 128

max_allowed_packet = 4M

binlog_cache_size = 1M

max_heap_table_size = 8M

tmp_table_size = 16M

read_buffer_size = 2M

read_rnd_buffer_size = 8M

sort_buffer_size = 8M

join_buffer_size = 8M

key_buffer_size = 4M

thread_cache_size = 8

query_cache_type = 1

query_cache_size = 8M

query_cache_limit = 2M

ft_min_word_len = 4

#############for mysql replication##########

log-bin = mysql-bin

log-bin-index = mysql-bin

relay-log = relay-log

relay-log-index = relay-log

binlog-format=ROW

log-slave-updates=true

gtid-mode=on

enforce-gtid-consistency=true

master-info-repository=TABLE

relay-log-info-repository=TABLE

sync-master-info=1

slave-parallel-workers=2

binlog-checksum=CRC32

master-verify-checksum=1

slave-sql-verify-checksum=1

binlog-rows-query-log_events=1

###############################################33

expire_logs_days = 30

log_error = /data/mysql/mysql-error.log

slow_query_log = 1

long_query_time = 1

slow_query_log_file = /data/mysql/mysql-slow.log

performance_schema = 0

explicit_defaults_for_timestamp

#lower_case_table_names = 1

skip-external-locking

default_storage_engine = InnoDB

#default-storage-engine = MyISAM

innodb_file_per_table = 1

innodb_open_files = 500

innodb_buffer_pool_size = 64M

innodb_write_io_threads = 4

innodb_read_io_threads = 4

innodb_thread_concurrency = 0

innodb_purge_threads = 1

innodb_flush_log_at_trx_commit = 2

innodb_log_buffer_size = 2M

innodb_log_file_size = 32M

innodb_log_files_in_group = 3

innodb_max_dirty_pages_pct = 90

innodb_lock_wait_timeout = 120

bulk_insert_buffer_size = 8M

myisam_sort_buffer_size = 8M

myisam_max_sort_file_size = 10G

myisam_repair_threads = 1

interactive_timeout = 28800

wait_timeout = 28800

[mysqldump]

quick

max_allowed_packet = 16M

[myisamchk]

key_buffer_size = 8M

sort_buffer_size = 8M

read_buffer = 4M

write_buffer = 4M

b. /usr/local/mysql/bin/mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql         -------注意5.7之前的版本用mysql_install_db初始化

例如：./scripts/mysql_install_db --datadir=/data/mysql5.6/ --user=mysql --basedir=/usr/local/mysql5.6/ --defaults-file=/etc/my.cnf.5.6

启动: /usr/local/mysql5.6/bin/mysqld_safe --defaults-file=/etc/my56.cnf &

关闭：mysqladmin –u root  -h 127.0.0.1 –port 3307 shutdown

mysqladmin -uroot -p1234#Byd  -S /tmp/mysql5.6.sock shutdown

安装多个实例，旧版本初始化时候要指定—defaults-file

然后初始化密码时候也要指定对应的端口或者socket，例如:

/usr/local/mysql5.6/bin/mysqladmin -u root -h 127.0.0.1 --port 3307 password 'password'

/usr/local/mysql5.6/bin/mysqladmin -u root --socket --port 3307 password 'password'

c. 启动mysql， /usr/local/mysql/bin/mysqld_safe &

d. 设置mysql root登陆密码：/usr/local/mysql/bin/mysql -e "grant all privileges on *.* to root@'127.0.0.1' identified by \"1234#Byd\" with grant option;"

/usr/local/mysql/bin/mysql -e "grant all privileges on *.* to root@'localhost' identified by \"1234#Byd\" with grant option;"

e. 其他，配置mysql服务以及mysql bin环境变量

cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql

chmod 755 !$

可以通过 service mysql start/status/stop 管理

ln  -s  /usr/local/mysql/bin/* /usr/local/bin/

登陆Mysql:mysql -u root -p1234#Byd

二. 主从配置

1. 安装上面的操作配置主从，先把mysql先配置了(下面主从仅适用于新安装的，已有的数据库，要求有配置server-id,以及开启二进制日志的前提下才可以配置主从，需要先把主库导出，然后配置同步)

2. 主从差异，配置文件server-id不同即可

3. 在主库上创建用于复制的用户

grant replication slave on *.* to slave@'10.9.31.%' identified by 'slave#2016?';

4. 主库查询状态：查询当前日志文件及position，用于日志恢复

5、从库配置到主库的复制

change master to master_host='10.9.31.27',master_user='slave',master_password='slave#2016?',master_log_file='mysql-bin.000004',master_log_pos=1225;

开启复制：start slave;

查看复制状态：show slave status\G; 查询到复制的IO和SQL线程都为YES表示配置成功。

6. 验证

在主库创建库或者表，在从库查询。

注意：5.6以上版本开启了GTID特性，mysql自己识别复制log及pos，方便管理；由于上面my.cnf已经开启了gtid，可以使用该特性完成复制

1：配置账号

grant replication slave,replication client on *.* to repl@'10.9.31.29' identified by 'Repl#2016';

change master to master_host='10.9.31.26',master_port=3306,master_user='repl',master_password='Repl#2016',master_auto_position=1;

启动复制

start slave;

show slave status\G;

show processeslist;