xml地图|网站地图|网站标签 [设为首页] [加入收藏]

能源节能

当前位置:手机网投123 > 能源节能 > 【能源节能】mysql-5.6.22的安装步骤

【能源节能】mysql-5.6.22的安装步骤

来源:http://www.hshlvy.com 作者:手机网投123 时间:2019-09-24 13:51

2. 修改 IO 调度器设置
io 调度器修改为 deadline,如果是 SSD 或者 PCIe-SSD 设备,则修改为 noop,下列两种方式修改: a) 在线动态修改,重启失效

| root | localhost |          |

修改 /etc/grub.conf 配置文件,在 kernel 那行增加一个配置,例如: kernel /vmlinuz-2.6.18-308.el5 ro root=LABEL=/1 elevator=deadline rhgb quiet

groupadd mysql

#端口号为 3306 的实例特殊配置 [mysqld3306]
#指定本实例相应版本的 basedir 和 datadir basedir= /usr/local/mysql-5.5.37

 

#innodb
innodb_buffer_pool_size = 1G innodb_buffer_pool_instances = 1 innodb_data_file_path = ibdata1:1G:autoextend innodb_flush_log_at_trx_commit = 1 innodb_log_buffer_size = 64M innodb_log_file_size = 256M innodb_log_files_in_group = 2 innodb_max_dirty_pages_pct = 50 innodb_file_per_table = 1 innodb_rollback_on_timeout innodb_status_file = 1
innodb_io_capacity = 2000 transaction_isolation = READ-COMMITTED innodb_flush_method = O_DIRECT

[mysql]
prompt="\u@\h:p  \R:\m:\s [\d]>"
#tee=/data/mysql/mysql_3306/data/query.log
no-auto-rehash

tee=/home/mysql/query.log no-auto-rehash

19、查看mysql用户的表信息:

socket = /home/mysql/mysql.sock event_scheduler = 0

cd /opt/mysql/mysql-5.6.22

CPU 启用节能模式后,会节约电量,但也可能带来 CPU 性能下降的问题。因此,运行数据库类业务时,建议关闭节能模式,发挥 CPU 的最大 性能。

[mysqld_multi]
mysqld = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin
log = /opt/mysql/mysqld_multi.log

read_rnd_buffer_size = 16M bulk_insert_buffer_size = 32M

 

  1. datadir 目录:
    单实例: /data/mysql
    多实例: /data/mysql/实例名_端口号,例如 /data/mysql/mytest_3306 或者 /data/mysql/yejr_3307

14、初始化数据库:

或者选择搜狐国内镜像站,下载速度较快:
该镜像站点已经无法下载 5.5.37 版本,选择最新的 5.5.39 也是可以的:

6、查看结果:

#buffers & cache table_open_cache = 2048 table_definition_cache = 2048 table_open_cache = 2048 max_heap_table_size = 96M sort_buffer_size = 2M join_buffer_size = 2M thread_cache_size = 256 query_cache_size = 0 query_cache_type = 0 query_cache_limit = 256K query_cache_min_res_unit = 512 thread_stack = 192K tmp_table_能源节能,size = 96M key_buffer_size = 8M read_buffer_size = 2M read_rnd_buffer_size = 16M bulk_insert_buffer_size = 32M

socket = mysql.sock

 

[mysqld_multi]
mysqld = /usr/local/mysql /bin/mysqld_safe mysqladmin = /usr/local/mysql /bin/mysqladmin log = /home/mysql/mysqld_multi.log

 

datadir = /data/mysql/yejr_3306 #重新配置这几个选项,不与全局配置一样,会直接覆盖上面的全局设置

chkconfig --add mysql

innodb_buffer_pool_size = 4G transaction_isolation = REPEATABLE-READ

 

MySQL 运行环境建议规范

| root | master1   |          |

8、/etc/my.cnf 中,[mysqld] section 默认选项 #my.cnf
[client]
port = 3306

 

#binlog
binlog_format = mixed server-id = 10518
log-bin = mybinlog binlog_cache_size = 4M max_binlog_size = 1G max_binlog_cache_size = 2G sync_binlog = 1 expire_logs_days = 10

 

最后,查看核实下 io 调度器的配置,例如:
cat /sys/block/sda/queue/scheduler
noop anticipatory deadline [cfq]
这时表示 io 调度器采用的是 cfq,而非我们要求的 deadline,需要及时调整。

 

1. 软件安装目录
mysql 预编译包默认安装路径(basedir) /usr/local/mysql,实际路径可能是 /usr/local/mysql-5.6.16-linux-glibc2.5-x86_64,软链接成 /usr/local/mysql,即 ln -s /usr/local/mysql-5.6.16-linux-glibc2.5-x86_64 /usr/local/mysql

/etc/init.d/mysql start

3. 多实例管理默认采用 mysqld_multi 方式
my.cnf 配置文件中, [mysqld] 中是一些公共配置,例如: #my.cnf
[client]
port = 3306
socket = mysql.sock

|      | localhost |          |

内核参数建议值

ll /usr/local/

swappiness 是 Linux 的一个内核参数,用来控制 Linux 物理 RAM 内存进行 SWAP 页交换的相对权重,尽量减少系统的页缓存被从内存中清除的情况。 取值范围是0~100,vm.swappiness 的值越低,Linux 内核会尽量不进行 SWAP 交换页的操作,vm.swappiness 的值越高,linux 会越多的使用 SWAP 空 间。Linux 系统的默认值是 60,当系统需要内存时,有 60%的概率使用 SWAP。对于大多数桌面系统,设置为 100 可以提高系统的整体性能;对于数 据库应用服务器,设置为 0,可以提高物理内存的使用率,进而提高数据库服务的响应性能。

20、删除初始化用户:

操作系统版本选择

source .bash_profile

一、操作系统环境

[mysqld]
#misc
user = mysql
basedir = /usr/local/mysql
datadir = /data/mysql/mysql_3306/data
port = 3306
socket = /tmp/mysql.sock
event_scheduler = 0

#timeout interactive_timeout = 300 wait_timeout = 300

mkdir -p /opt/mysql/mysql-5.6.22

二、MySQL 环境 环境规范定义

id mysql

#myisam

15、将启动信息加入到启动像

socket = /home/mysql/mysql.sock event_scheduler = 0

一、环境与下载地址:

#binlog
binlog_format = mixed server-id = 10518
log-bin = mybinlog binlog_cache_size = 4M max_binlog_size = 1G max_binlog_cache_size = 2G sync_binlog = 1

explicit_defaults_for_timestamp=1
#buffers & cache
table_open_cache = 2048
table_definition_cache = 2048
table_open_cache = 2048
max_heap_table_size = 96M
sort_buffer_size = 2M
join_buffer_size = 2M
thread_cache_size = 256
query_cache_size = 0
query_cache_type = 0
query_cache_limit = 256K
query_cache_min_res_unit = 512
thread_stack = 192K
tmp_table_size = 96M
key_buffer_size = 8M
read_buffer_size = 2M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 32M

character-set-server = utf8

 

#myisam myisam_sort_buffer_size = 128M myisam_max_sort_file_size = 10G myisam_repair_threads = 1

9、查看依赖的lib库是否安全

max_relay_log_size = 1G relay_log_purge = 1 relay_log_recovery = 1 log_slave_updates #slave-skip-errors=1032,1053,1062

18、添加启动项:

参考 my.cnf 配置文件

初始化数据库一定要在basedir 下进行

[mysqld]
#misc
user = mysql
basedir = /usr/local/mysql datadir = /home/mysql port = 3306

12、编辑my.cnf

不过需要注意的是,RHEL 7 以上版本,如果 vm.swappiness = 0,有可能会由于 OOM 问题,导致 mysqld 进程被 OOM-Killer 进程杀掉。 关闭 NUMA 特性

 

open_files_limit = 65535 max_connections = 100

 

也可以根据个人习惯下载源码包或者 RPM 包。

 

#innodb
innodb_buffer_pool_size = 1G innodb_buffer_pool_instances = 1 innodb_data_file_path = ibdata1:1G:autoextend innodb_flush_log_at_trx_commit = 1 innodb_log_buffer_size = 64M innodb_log_file_size = 256M innodb_log_files_in_group = 2 innodb_max_dirty_pages_pct = 50 innodb_file_per_table = 1 innodb_rollback_on_timeout innodb_status_file = 1
innodb_io_capacity = 2000 transaction_isolation = READ-COMMITTED innodb_flush_method = O_DIRECT 

echo “deadline” > /sys/block/sdb/queue/scheduler

explicit_defaults_for_timestamp = true

默认地,所有配置参数都放在 /etc/my.cnf 中,不建议每个实例自己一个配置文件。下面是参考的 my.cnf 配置文件: #[mysql]
[mysql]
prompt="\u@\h \D \R:\m:\s [\d]> "

或者

1. 调整最大文件数限制 ulimit -n 65535

 

max_connect_errors = 100000

b、查看mysql的监听:

#timeout interactive_timeout = 300 wait_timeout = 300

cd /usr/local/mysql/

#logs
log-output=file
slow_query_log = 1
slow_query_log_file = /home/mysql/slow.log log-error = /home/mysql/error.log log_warnings = 2
pid-file = /home/mysql/mysql.pid long_query_time = 1 #log-slow-admin-statements = 1 #log-queries-not-using-indexes = 1 log-slow-slave-statements = 1

 

#relay log skip_slave_start = 1

[mysqld3308]
port=3308
server-id=203308
#binlog-do-db=db01
basedir= /usr/local/mysql
datadir = /data/mysql/mysql_3308/data
socket          = /tmp/mysql_3308.sock
#重新配置这几个选项,不与全局配置一样,会直接覆盖上面的全局设置
innodb_buffer_pool_size = 100m
innodb_flush_log_at_trx_commit = 2
sync_binlog = 0

挂载点

大小

分区类型

分区用途

/boot

100M

系统默认即可

/boo

swap

4G-16G

swap

swap

/opt

20G

xfs

安装应用软件
应用程序安装在 /opt/app/ 下的子目录,例如 /opt/app/mysql-5.5.37

/

40G ,最小不低于 20G

系统默认即可

/

/home

第一个 Raid 剩余 ,不低于 20G

xfs

存放备份文件目录
备份文件放在 /home/backup 下的子目录,例如 /home/backup/mysql/20140819

/data

第二个 Raid 的全部,根据数据大小决定

xfs

存放 MySQL 数据文件目录
数据文件放在 /data/mysql 下的子目录,例如

 

--log-error="$errlogfile" --pid-file="$mypidfile" --user=mysql >/dev/null 2>&1 &

delete from mysql.user where user!='root' or host!='localhost';

myisam_sort_buffer_size = 128M myisam_max_sort_file_size = 10G myisam_repair_threads = 1

#innodb
innodb_buffer_pool_size = 100M
innodb_buffer_pool_instances = 1
innodb_data_file_path = ibdata1:100M:autoextend(生产中最少1G)
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 64M
innodb_log_file_size = 64M(生产中最大256)
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 50
innodb_file_per_table = 1
innodb_rollback_on_timeout
innodb_status_file = 1
innodb_io_capacity = 2000
transaction_isolation = REPEATABLE-READ
innodb_flush_method = O_DIRECT

[mysqld_multi]
mysqld = /usr/local/mysql /bin/mysqld_safe mysqladmin = /usr/local/mysql /bin/mysqladmin log = /home/mysql/mysqld_multi.log

 

关闭 CPU 的节能模式

 

CentOS/RHRL/ORACLE Linux 5.x/6.x x86_64 发行版 建议磁盘分区规则

推荐使用官方

并且写入 /etc/sysctl.conf,重启后也能生效

2、创建一个MySQL用户组:

#pager="less -i -n -S" no-auto-rehash tee="/home/mysql/tee.log"

1、初始化mysql的时候,需要开启另一个连接,tail -f error.log,根据报错进行查看。

b) 修改 /etc/init.d/mysql 或者 mysqld_safe 脚本,设定启动 mysqld 进程时的 NUMA 调度机制,例如: numactl --interleave=all /usr/bin/mysqld_safe --datadir="$datadir" --socket="$socketfile"

 

这里的 sdb 修改为实际的设备名称,例如 sda,或者 sdc。 b) 修改 /etc/grub.conf,永久生效

vi .bash_profile

新一代架构的 NUMA 不适用于跑数据库的场景。它本意是为了提高内存利用率,但实际效果不好,反而可能导致一个 CPU 的内存尚有剩余, 但另一个不够用,发生 SWAP 的问题,因此建议直接关闭或者修改 NUMA 的调度机制。
a) 修改 /etc/grub.conf,关闭 NUMA,重启后生效

 

[mysql]
prompt="\u@\h \D \R:\m:\s [\d]> #pager="less -i -n -S" tee=/home/mysql/query.log no-auto-rehash

#character set
character-set-server = utf8

open_files_limit = 65535 max_connections = 100 max_connect_errors = 100000

 

#logs
log-output=file
slow_query_log = 1
slow_query_log_file = /home/mysql/slow.log log-error = /home/mysql/error.log log_warnings = 2
pid-file = /home/mysql/mysql.pid long_query_time = 1 #log-slow-admin-statements = 1 #log-queries-not-using-indexes = 1 log-slow-slave-statements = 1

 

[mysqld]
#misc
user = mysql
basedir = /usr/local/mysql datadir = /home/mysql port = 3306

4、查看用户和组状态:

MySQL 安装包下载地址: 官方地址: 选择:Archived versions »

cp my.cnf  /etc/my.cnf

3. 修改 swappiness 设置 vm.swappiness = 0

2、查看my.cnf文件里的目录是否都存在。

建议下载预编译好的二进制安装包:Linux - Generic 2.6 (x86, 64-bit), Compressed TAR Archive

 

#buffers & cache table_open_cache = 2048 table_definition_cache = 2048 table_open_cache = 2048 max_heap_table_size = 96M sort_buffer_size = 2M join_buffer_size = 2M thread_cache_size = 256 query_cache_size = 0 query_cache_type = 0 query_cache_limit = 256K query_cache_min_res_unit = 512 thread_stack = 192K tmp_table_size = 96M key_buffer_size = 8M read_buffer_size = 2M

+------+-----------+----------+

#character set character-set-server = utf8

 

修改 /etc/grub.conf 配置文件,在 kernel 那行增加一个配置后重启生效,例如: kernel /vmlinuz-2.6.18-308.el5 ro root=LABEL=/1 elevator=deadline numa=off rhgb quiet

 

[mysqld3307]
basedir= /usr/local/mysql-5.5.39
datadir = /data/mysql/yejr_3307 #重新配置这几个选项,不与全局配置一样,会直接覆盖上面的全局设置 innodb_buffer_pool_size = 2G
innodb_flush_log_at_trx_commit = 2
sync_binlog = 0

7、解压mysql的安装包:

并且将该设置写入 /etc/sysctl.conf ,重启后也能生效。

open_files_limit = 65535
max_connections = 100
max_connect_errors = 100000

 

| root | ::1       |          |

#relay log
skip_slave_start = 1 max_relay_log_size = 1G relay_log_purge = 1 relay_log_recovery = 1 log_slave_updates #slave-skip-errors=1032,1053,1062

22、检查权限:

#character set

#timeout
interactive_timeout = 300
wait_timeout = 300

[mysql]
prompt="\u@\h \D \R:\m:\s [\d]> #pager="less -i -n -S"

=========================================================

     
/data/mysql/myapp_3306 

/tmp

8-16G

tmpfs

采用 tmpfs,利用内存的共享内存,加速 /tmp 目录的文件读写性能

chkconfig --level 35 mysql on

转自吴老师 

 

expire_logs_days = 10

#relay log
skip_slave_start = 1
max_relay_log_size = 1G
relay_log_purge = 1
relay_log_recovery = 1
log_slave_updates
#slave-skip-errors=1032,1053,1062

然后选择:MySQL Community Server
再选择 5.5.37 版本及对应的操作系统分支 Linux - Generic:

1、卸载原版本的mysql:

 

+------+-----------+----------+

chown -R mysql:mysql /usr/local/mysql

 

select user,host,password from mysql.user;

 

21、刷新数据库:

export PATH=/usr/local/mysql/bin:/opt/mysql/mysql-5.6.22/bin:$HOME/bin:$PATH

安装时出现的问题大多数的排错思路如下:

cd  /usr/local/mysql

 

ln -s /opt/mysql/mysql-5.6.22  /usr/local/mysql

 

17、查看mysql是否启动成功:

ldd /usr/local/mysql/bin/mysqld

 

 

2、mysql下载地址:

useradd -g mysql -s /sbin/nologin -d /opt/mysql mysql

 

netstat -nalp|grep "3306"

 

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

 

 

 

 

 

lrwxrwxrwx  1 root root   23 Dec 17 15:24 mysql -> /opt/mysql/mysql-5.6.22

 

| user | host      | password |

ll /usr/local/

 

flush privileges;

16、启动mysql服务:

#my.cnf
[client]
port            = 3306
socket          = /tmp/mysql.sock

 

skip-name-resolve = 1
#logs
log-output=file
slow_query_log = 1
slow_query_log_file = slow.log
log-error = /data/mysql/mysql_3306/data/error.log
log_warnings = 2
pid-file = mysql.pid
long_query_time = 1
#log-slow-admin-statements = 1
#log-queries-not-using-indexes = 1
log-slow-slave-statements = 1

3、查看my.cnf文件里的目录权限是否都是mysql.mysql。

 

ps axu|grep mysqld

 

 

 

 

 

 

 

yum -y remove mysql-libs-5.1*

 

select user,host,password from mysql.user;

 

 

 

| root | 127.0.0.1 |          |

 

chkconfig --list |grep mysql

 

11、设置root的环境变量:

 

 

a、查看mysql的进程:

chown -R mysql:mysql /data/mysql

13、复制my.cnf到/etc/目录下:

mysql           0:off 1:off 2:on 3:on 4:on 5:on 6:off

 

5、配置myql的安装目录:

|      | master1   |          |

#binlog
binlog_format = mixed
server-id = 203306
log-bin = /data/mysql/mysql_3306/logs/mybinlog
binlog_cache_size = 4M
max_binlog_size = 1G
max_binlog_cache_size = 2G
sync_binlog = 0
expire_logs_days = 10

 

uid=500(mysql) gid=500(mysql) groups=500(mysql)

[mysqld3309]
port=3309
server-id=203309
#binlog-do-db=db01
basedir= /usr/local/mysql
datadir = /data/mysql/mysql_3309/data
socket          = /tmp/mysql_3309.sock
#重新配置这几个选项,不与全局配置一样,会直接覆盖上面的全局设置
innodb_buffer_pool_size = 100m
innodb_flush_log_at_trx_commit = 2
sync_binlog = 0

 

8、剪贴文件到安装目录:

6 rows in set (0.00 sec)

+------+-----------+----------+

chown -R mysql:mysql /opt/mysql

mkdir -p /data/mysql/mysql_3306/{data,tmp,logs}

 

 

 

#端口号为3306的实例特殊配置
[mysqld3306]
port =3306
server-id=203306
#指定本实例相应版本的basedir和datadir
basedir= /usr/local/mysql
datadir = /data/mysql/mysql_3306/data
socket          = /tmp/mysql_3306.sock
#重新配置这几个选项,不与全局配置一样,会直接覆盖上面的全局设置
innodb_buffer_pool_size = 100m
#transaction_isolation = REPEATABLE-READ

3、一个属于MySQL的用组的mysql用户

 

rpm -e  原始mysql

 

mv mysql-5.6.22-linux-glibc2.5-x86_64/* .

tar -xzvf /data/soft/mysql-5.6.22-linux-glibc2.5-x86_64.tar.gz -C /opt/mysql/mysql-5.6.22/

1、系统下载地址:

4、如果是虚拟机查看内存和磁盘是否足够。

10、更改权限:

./scripts/mysql_install_db --user=mysql --defaults-file=/etc/my.cnf

二、安装mysql的步骤:

#myisam
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1

本文由手机网投123发布于能源节能,转载请注明出处:【能源节能】mysql-5.6.22的安装步骤

关键词: