mysql安装部长普_MySQL的安装

mysql5.7安装(二进制)

注意：安装之前请务必删除/etc/my.cnf文件 和/etc/my.cnf.d 文件夹，不然初始化会有问题

unlink 可以取消软连接

shell> yum -y install ld-linux.so.2 libaio.so.1 libnuma.so.1 libstdc++.so.6 libncurses.so.5

shell> groupadd mysql

shell> useradd -r -g mysql -s /bin/false mysql

shell> cd /usr/local

shell> tar zxvf /path/to/mysql-VERSION-OS.tar.gz

shell> ln -s full-path-to-mysql-VERSION-OS mysql

shell> cd mysql

shell> mkdir mysql-files

shell> chown mysql:mysql mysql-files

shell> chmod 750 mysql-files

shell> bin/mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql #注意此处有临时密码，记住

shell> bin/mysql_ssl_rsa_setup #5.7中要初始化ssl安全连接

#ssl连接使用mysql --ssl 来连接 对于socket是没有用的 只有通过ip连接的时候才会生效

shell> bin/mysqld_safe --user=mysql & #使用mysqld_safe 启动 mysqld_safe是mysqld的一个守护进程

# Next command is optional

shell> cp support-files/mysql.server /etc/init.d/mysqld

shell> echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh

#修改密码

#mysql5.7不需要使用password加密，自动加密

mysql> set password='123';

chkconfig --add mysqld

ERROR 1862 (HY000): Your password has expired. To log in you must change it using a client that supports expired passwords.

解决办法：/usr/local/mysql/bin/mysqladmin -u root -p password 设置密码

Enter password:

New password:

Confirm new password:

Mysql 5.7在初始化安装完成之后去掉了test库 增加了一个sys库，主要是为了安全起见，因为test库所有人都有权限。sys库主要是向oracle看齐

mysql5.6安装(二进制)

注意：安装之前请务必删除/etc/my.cnf文件 和/etc/my.cnf.d 文件夹，不然初始化会有问题

shell> groupadd mysql

shell> useradd -r -g mysql -s /bin/false mysql

shell> cd /usr/local

shell> tar zxvf /path/to/mysql-VERSION-OS.tar.gz

shell> ln -s full-path-to-mysql-VERSION-OS mysql

shell> cd mysql

shell> scripts/mysql_install_db --user=mysql //最关键，初始化mysql数据库

shell> bin/mysqld_safe --user=mysql &

# Next command is optional

shell> cp support-files/mysql.server /etc/init.d/mysqld

shell> echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh

#修改密码

mysql> set password=password('123');

chkconfig --add mysqld

mysqld_safe其实是一个守护进程，它会去启动一个mysqld进程，如果mysqld进程挂掉的话，mysqld_safe会去重新启动mysqld进程，如果通过mysqld直接启动mysql就不会在进程挂掉的时候重启

my.cnf(适合8G内存服务器)

mysql默认配置文件路径，可以通过mysqld --help -v | grep my.cnf 查看

默认以这个路径读取配置文件/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf

my.cnf, $MYSQL_TCP_PORT, /etc/services, built-in default

如果这几个配置文件同时存在，最后的会覆盖前面的配置文件中同样的内容 如果想使用指定的配置文件，可以使用--defaults-file=# Only read default options from the given file #.选项指定配置文件

my.cnf

[mysqld]

######basic settings#######

server-id = 1

port = 3306

user = mysql

#autocommit = 0 #如果5.6安装之后需要密码，注释这一行 自动提交sql

character_set_server = utf8mb4

skip_name_resolve = 1

max_connections = 800

max_connect_errors = 1000

datadir = /data/mysql

transaction_isolation = READ-COMMITTED

explicit_defaults_for_timestamp = 1

join_buffer_size = 134217728

tmp_table_size = 67108864

tmpdir = /tmp

max_allowed_packet = 16777216

sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER"

interactive_timeout = 1800

wait_timeout = 1800

read_buffer_size = 16777216

read_rnd_buffer_size = 33554432

sort_buffer_size = 33554432

lower_case_table_names = 1

#####log settings#####

log_error = error.log

slow_query_log = 1 #开启慢查询日志

slow_query_log_file = slow.log

log_queries_not_using_indexes = 1 #将没有使用索引的sql记录到慢查询日志

log_slow_admin_statements = 1 #记录管理操作，如alter/analyze table

log_slow_slave_statements = 1 #在从服务器上开启慢查询日志

log_throttle_queries_not_using_indexes = 10 #限制每分钟记录没有使用索引sql语句的次数

expire_logs_days = 90

long_query_time = 2 #记录大于2秒的日志

min_examined_row_limit = 100 #扫描记录少于该值的sql语句不记录到慢查询日志

log_timestamps = 1 #写入时区信息

#####replication settings ####

sync_binlog = 1

log_bin = bin.log

relay_log_info_repository = TABLE

master_info_repository = TABLE

gtid_mode = on

enforce_gtid_consistency = 1

log_slave_updates

binlog_format = row

relay_log = relay.log

relay_log_recovery = 1

binlog_gtid_simple_recovery = 1

slave_skip_errors = ddl_exist_errors

##### innodb settings #####

innodb_page_size = 8192

innodb_buffer_pool_size = 6G #如果小于8G内存 请注释掉该行，不然可能启动不了

innodb_buffer_pool_instances = 8

innodb_buffer_pool_load_at_startup = 1

innodb_buffer_pool_dump_at_shutdown = 1

innodb_lru_scan_depth = 2000 #控制LRU列表中可用页的数量，默认为1024

innodb_lock_wait_timeout = 5

innodb_io_capacity = 4000 #iops能力，需要测试磁盘iops

innodb_io_capacity_max = 8000

innodb_flush_method = O_DIRECT

innodb_file_format = Barracuda

innodb_file_format_max = Barracuda

#innodb_log_group_home_dir = /redolog/

#innodb_undo_directory = /undolog/

innodb_undo_logs = 128

innodb_undo_tablespaces = 3

innodb_flush_neighbors = 1 #在固态硬盘下设置为0

innodb_log_file_size = 4G

innodb_log_buffer_size = 16777216

innodb_purge_threads = 1

innodb_large_prefix = 1

innodb_thread_concurrency = 64

innodb_print_all_deadlocks = 1

innodb_strict_mode = 1

innodb_sort_buffer_size = 67108864

###semi sync replication settings ###

plugin_dir = /usr/local/mysql/lib/plugin

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

#将错误日志写到系统日志文件

[mysqld_safe]

syslog

syslog_tag = stock

[mysql-5.7]

innodb_buffer_pool_dump_pct = 40

innodb_page_cleaners = 4

innodb_purge_threads = 4

innodb_undo_log_truncate = 1

innodb_max_undo_log_size = 2G

innodb_purge_rseg_truncate_frequency = 128

binlog_gtid_simple_recovery = 1

log_timestamps = system

transaction_write_set_extraction = MURMUR32

show_compatibility_56 = on

mysql -V 可以查看mysql的版本 使用mysqldump的时候要注意版本 如果使用低版本的mysqldump 去备份高版本的mysql可能会出现问题

systemd服务管理方式service文件

cat /etc/systemd/system/mysqld.service

[Unit]

Description=MySQL Server

Documentation=man:mysqld(8)

After=network.target

After=syslog.target

[Install]

WantedBy=multi-user.target

[Service]

User=mysql

Group=mysql

ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf #写安装路径下的mysqld

LimitNOFILE=5000

systemctl daemon-reload #重新加载配置

systemctl start mysqld # 启动mysql

MySQL多实例安装

初始化多套数据

mv /etc/my.cnf /etc/my.cnf.bak

mysqld --initialize-insecure --user=mysql --datadir=/data/mysql/3307/data --basedir=/usr/local/mysql

mysqld --initialize-insecure --user=mysql --datadir=/data/mysql/3308/data --basedir=/usr/local/mysql

mysqld --initialize-insecure --user=mysql --datadir=/data/mysql/3309/data --basedir=/usr/local/mysql

使用systemd管理多实例(service文件在上面)

cd /etc/systemd/system

cp mysqld.service mysqld3307.service

cp mysqld.service mysqld3308.service

cp mysqld.service mysqld3309.service

修改systemd service中的ExecStart(为每个实例创建一个配置文件)

vim mysqld3307.service

ExecStart=/user/local/mysql/bin/mysqld --defaults-file=/data/mysql/3307/my.cnf

vim mysqld3308.service

ExecStart=/user/local/mysql/bin/mysqld --defaults-file=/data/mysql/3308/my.cnf

vim mysqld3309.service

ExecStart=/user/local/mysql/bin/mysqld --defaults-file=/data/mysql/3309/my.cnf

授权

chown -R mysql.mysql /data/*

启动

systemctl daemon-reload #重新加载配置

systemctl start mysqld3307.service

systemctl start mysqld3308.service

systemctl start mysqld3309.service