生产环境 xfs filesystem 上安装Mariadb_MySQL-mysql教程-PHP中文网

首页

数据库

mysql教程

生产环境 xfs filesystem 上安装Mariadb_MySQL

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

May 30, 2016 pm 05:10 PM

环境

规划

/dev/sda 安装Linux操作系统，CentOS-6.6

/dev/sdb Mysql数据文件和二进制文件单独放在一块硬盘，磁盘做成LVM逻辑卷方便以后扩充

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

| 1、查看磁盘 |

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

root@nginx1 ~ > fdisk -l /dev/sdb 数据盘，大小为300G

Disk /dev/sdb: 300.0 GB, 300000000000 bytes

255 heads, 63 sectors/track, 36472 cylinders

Units = cylinders of 16065 * 512 = 8225280 bytes

Sector size (logical/physical): 512 bytes / 512 bytes

I/O size (minimum/optimal): 512 bytes / 512 bytes

Disk identifier: 0xe562e562

Device Boot Start End Blocks Id System

root@nginx1 ~ >

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

|2、进行分区，并配置LVM|

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

1) 进行分区

root@nginx1 ~ > fdisk /dev/sdb

WARNING: DOS-compatible mode is deprecated. It's strongly recommended to

switch off the mode (command 'c') and change display units to

sectors (command 'u').

Command (m for help): p

Disk /dev/sdb: 300.0 GB, 300000000000 bytes

255 heads, 63 sectors/track, 36472 cylinders

Units = cylinders of 16065 * 512 = 8225280 bytes

Sector size (logical/physical): 512 bytes / 512 bytes

I/O size (minimum/optimal): 512 bytes / 512 bytes

Disk identifier: 0xe562e562

Device Boot Start End Blocks Id System

Command (m for help): n

Command action

e extended

p primary partition (1-4)

Partition number (1-4): 1

First cylinder (1-36472, default 1):

Using default value 1

Last cylinder, +cylinders or +size{K,M,G} (1-36472, default 36472):

Using default value 36472

Command (m for help): t

Selected partition 1

Hex code (type L to list codes): 8e

Changed system type of partition 1 to 8e (Linux LVM)

Command (m for help): p

Disk /dev/sdb: 300.0 GB, 300000000000 bytes

255 heads, 63 sectors/track, 36472 cylinders

Units = cylinders of 16065 * 512 = 8225280 bytes

Sector size (logical/physical): 512 bytes / 512 bytes

I/O size (minimum/optimal): 512 bytes / 512 bytes

Disk identifier: 0xe562e562

Device Boot Start End Blocks Id System

/dev/sdb1 1 36472 292961308+ 8e Linux LVM

Command (m for help):

Command (m for help): w

The partition table has been altered!

Calling ioctl() to re-read partition table.

Syncing disks.

root@nginx1 ~ > partprobe /dev/sdb

root@nginx1 ~ > cat /proc/partitions

major minor #blocks name

80 143374740 sda

81 204800 sda1

828388608 sda2

83 107315200 sda3

84 1 sda4

85 20971520 sda5

8 16 292968750 sdb

8 17 292961308 sdb1

root@nginx1 ~ >

2) 配置LVM

root@nginx1 ~ > pvcreate /dev/sdb1

Physical volume "/dev/sdb1" successfully created

root@nginx1 ~ > vgcreate mysql-vg /dev/sdb1

Volume group "mysql-vg" successfully created

root@nginx1 ~ > lvcreate -L 250G -n mysql-lv mysql-vg

Logical volume "mysql-lv" created

root@nginx1 ~ > lvs

LV VG Attr LSize Pool Origin Data% Meta% Move Log Cpy%Sync Convert

mysql-lv mysql-vg -wi-a----- 250.00g

root@nginx1 ~ >

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

|3、进行格式化===> 格式化为xfs 文件系统|

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

1) 由于默认CentOS内核就支持xfs文件系统，只需要加载模块即可

root@nginx1 ~ > lsmod |grep xfs

root@nginx1 ~ > modprobe xfs

root@nginx1 ~ > lsmod |grep xfs

xfs 1124960 0

exportfs4236 1 xfs

root@nginx1 ~ >

2) 安装客户端工具包

root@nginx1 ~ > yum -y install xfsprogs

3) 进行格式化

root@nginx1 ~ > mkfs.xfs /dev/mysql-vg/mysql-lv

meta-data=/dev/mysql-vg/mysql-lv isize=256agcount=4, agsize=16384000 blks

= sectsz=512 attr=2, projid32bit=0

data = bsize=4096 blocks=65536000, imaxpct=25

= sunit=0 swidth=0 blks

naming =version 2 bsize=4096 ascii-ci=0

log =internal log bsize=4096 blocks=32000, version=2

= sectsz=512 sunit=0 blks, lazy-count=1

realtime =none extsz=4096 blocks=0, rtextents=0

root@nginx1 ~ >

4) 修改磁盘调度策略为 deadline

root@nginx1 ~ > echo 'deadline' > /sys/block/sdb/queue/scheduler

root@nginx1 ~ > cat /sys/block/sdb/queue/scheduler

noop anticipatory [deadline] cfq

root@nginx1 ~ >

5) 挂载，挂载时禁止atime

root@nginx1 ~ > mkdir /JY

root@nginx1 ~ > mount -o noatime /dev/mysql-vg/mysql-lv /JY/

root@nginx1 ~ > df -HPT

Filesystem Type Size Used Avail Use% Mounted on

/dev/sda5 ext422G 1.5G 19G 8% /

tmpfs tmpfs 984M 0 984M 0% /dev/shm

/dev/sda1 ext4 199M 36M 154M 19% /boot

/dev/sda3 ext4 109G 2.3G 101G 3% /usr

/dev/mapper/mysql--vg-mysql--lv xfs269G 34M 269G 1% /JY

root@nginx1 ~ > mount

/dev/sda5 on / type ext4 (rw)

proc on /proc type proc (rw)

sysfs on /sys type sysfs (rw)

devpts on /dev/pts type devpts (rw,gid=5,mode=620)

tmpfs on /dev/shm type tmpfs (rw)

/dev/sda1 on /boot type ext4 (rw)

/dev/sda3 on /usr type ext4 (rw)

none on /proc/sys/fs/binfmt_misc type binfmt_misc (rw)

/dev/mapper/mysql--vg-mysql--lv on /JY type xfs (rw,noatime)

6) 写入fstab

root@nginx1 ~ > echo -e '/dev/mapper/mysql--vg-mysql--lv \t /JY \t xfs \t defaults,noatime \t 0 0' >> /etc/fstab

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

| 4、安装Mysql数据库 |

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

1) 创建Mysql用户

root@nginx1 ~ > useradd -r mysql -s /sbin/nologin

root@nginx1 ~ > mkdir /JY/{data,binlog}

root@nginx1 ~ > chown -R mysql:mysql /JY/

root@nginx1 ~ > ll /JY/

total 0

drwxr-xr-x 2 mysql mysql 6 Oct 22 10:44 binlog

drwxr-xr-x 2 mysql mysql 6 Oct 22 10:44 data

root@nginx1 ~ >

3) 安装cmkae

root@nginx1 ~ > tar xf cmake-3.0.1.tar.gz

root@nginx1 ~ > cd cmake-3.0.1

root@nginx1 ~/cmake-3.0.1 > ./configure

root@nginx1 ~/cmake-3.0.1 > make && make install

4) 安装数据库

root@nginx1 ~ > tar xf mariadb-10.0.21.tar.gz

root@nginx1 ~ > cd mariadb-10.0.21

root@nginx1 ~/mariadb-10.0.21 > cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \

-DMYSQL_DATADIR=/JY/data \

-DSYSCONFDIR=/etc \

-DWITH_INNOBASE_STORAGE_ENGINE=1 \

-DWITH_ARCHIVE_STORAGE_ENGINE=1 \

-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \

-DWITH_READLINE=1 \

-DWITH_SSL=system \

-DWITH_ZLIB=system \

-DWITH_LIBWRAP=0 \

-DMYSQL_UNIX_ADDR=/tmp/mysql.sock \

-DDEFAULT_CHARSET=utf8 \

-DDEFAULT_COLLATION=utf8_general_ci

root@nginx1 ~/mariadb-10.0.21 > make && make install

root@nginx1 ~/mariadb-10.0.21 > cd /usr/local/mysql/

root@nginx1 /usr/local/mysql > chown -R mysql:mysql ./*

root@nginx1 /usr/local/mysql >

root@nginx1 /usr/local/mysql > scripts/mysql_install_db --user=mysql --datadir=/JY/data/

root@nginx1 /usr/local/mysql > cp support-files/my-large.cnf /etc/my.cnf

root@nginx1 /usr/local/mysql > cp support-files/mysql.server /etc/init.d/mysqld

root@nginx1 /usr/local/mysql > chkconfig mysqld --add

root@nginx1 /usr/local/mysql > chkconfig mysqld on

root@nginx1 /usr/local/mysql > echo 'PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh

root@nginx1 /usr/local/mysql > chmod +x /etc/profile.d/mysql.sh

root@nginx1 /usr/local/mysql > source /etc/profile.d/mysql.sh

root@nginx1 /usr/local/mysql > echo '/usr/local/mysql/lib' > /etc/ld.so.conf.d/mysql.conf

root@nginx1 /usr/local/mysql > ldconfig

root@nginx1 /usr/local/mysql > ldconfig -v |grep mysql

/usr/local/mysql/lib:

libmysqlclient.so.18 -> libmysqlclient_r.so.18.0.0

/usr/lib64/mysql:

libmysqlclient_r.so.16 -> libmysqlclient_r.so.16.0.0

libmysqlclient.so.16 -> libmysqlclient.so.16.0.0

root@nginx1 /usr/local/mysql >

root@nginx1 /usr/local/mysql > ln -s /usr/local/mysql/include /usr/include/mysql

root@nginx1 ~ > /etc/init.d/mysqld start

Starting MySQL.[ OK ]

root@nginx1 ~ >

5) 配置Mysql配置文件

root@nginx1 ~ > vi /etc/my.cnf

root@nginx1 ~ > grep -v ^# /etc/my.cnf |grep -v ^$

[client]

port= 3306

socket = /tmp/mysql.sock

[mysqld]

port= 3306

socket = /tmp/mysql.sock

skip-external-locking

key_buffer_size = 256M

max_allowed_packet = 1M

table_open_cache = 256

sort_buffer_size = 1M

read_buffer_size = 1M

read_rnd_buffer_size = 4M

myisam_sort_buffer_size = 64M

thread_cache_size = 8

query_cache_size= 16M

thread_concurrency = 4

datadir = /JY/data

innodb_file_per_table = 1

log_error = /JY/data/jy.err

general_log = ON

general_log_file = /JY/data/general.log

slow_query_log = ON

slow_query_log_file = /JY/data/jy_slow.log

innodb_buffer_pool_size = 1024M

innodb_log_file_size = 512M

innodb_buffer_pool_instances=4

innodb_read_io_threads = 8

innodb_write_io_threads = 8

max_allowed_packet = 128M

innodb_flush_method=O_DIRECT

max_connections = 1000

max_user_connections = 1000

skip_name_resolve = ON

transaction_isolation = READ-COMMITTED

log-bin=/JY/binlog/jy-bin

binlog_format=mixed

server-id = 1

[mysql]

no-auto-rehash

[myisamchk]

key_buffer_size = 128M

sort_buffer_size = 128M

read_buffer = 2M

write_buffer = 2M

[mysqlhotcopy]

interactive-timeout

6) 重启Mysql

root@nginx1 ~ > /etc/init.d/mysqld restart

Shutting down MySQL... [ OK ]

Starting MySQL.............. [ OK ]

root@nginx1 ~ >

7) 设置Mysql密码

MariaDB [mysql]> drop user ''@'localhost';

Query OK, 0 rows affected (0.00 sec)

MariaDB [mysql]> drop user ''@'nginx1.tianxiang.com';

Query OK, 0 rows affected (0.00 sec)

MariaDB [mysql]> select user,password,host from user;

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

| user | password | host |

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

| root | | localhost|

| root | | nginx1.tianxiang.com |

| root | | 127.0.0.1|

| root | | ::1 |

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

4 rows in set (0.00 sec)

MariaDB [mysql]> grant all privileges on *.* to 'root'@'192.168.6.%' identified by 'fangyu421';

Query OK, 0 rows affected (0.05 sec)

MariaDB [mysql]> flush privileges;

Query OK, 0 rows affected (0.00 sec)

MariaDB [mysql]>

声明

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系admin@php.cn

MySQL：世界上最受欢迎的数据库的简介Apr 12, 2025 am 12:18 AM

MySQL是一种开源的关系型数据库管理系统，主要用于快速、可靠地存储和检索数据。其工作原理包括客户端请求、查询解析、执行查询和返回结果。使用示例包括创建表、插入和查询数据，以及高级功能如JOIN操作。常见错误涉及SQL语法、数据类型和权限问题，优化建议包括使用索引、优化查询和分表分区。

MySQL的重要性：数据存储和管理Apr 12, 2025 am 12:18 AM

MySQL是一个开源的关系型数据库管理系统，适用于数据存储、管理、查询和安全。1.它支持多种操作系统，广泛应用于Web应用等领域。2.通过客户端-服务器架构和不同存储引擎，MySQL高效处理数据。3.基本用法包括创建数据库和表，插入、查询和更新数据。4.高级用法涉及复杂查询和存储过程。5.常见错误可通过EXPLAIN语句调试。6.性能优化包括合理使用索引和优化查询语句。

为什么要使用mysql？利益和优势Apr 12, 2025 am 12:17 AM

选择MySQL的原因是其性能、可靠性、易用性和社区支持。1.MySQL提供高效的数据存储和检索功能，支持多种数据类型和高级查询操作。2.采用客户端-服务器架构和多种存储引擎，支持事务和查询优化。3.易于使用，支持多种操作系统和编程语言。4.拥有强大的社区支持，提供丰富的资源和解决方案。

描述InnoDB锁定机制（共享锁，独家锁，意向锁，记录锁，间隙锁，下一键锁）。Apr 12, 2025 am 12:16 AM

InnoDB的锁机制包括共享锁、排他锁、意向锁、记录锁、间隙锁和下一个键锁。1.共享锁允许事务读取数据而不阻止其他事务读取。2.排他锁阻止其他事务读取和修改数据。3.意向锁优化锁效率。4.记录锁锁定索引记录。5.间隙锁锁定索引记录间隙。6.下一个键锁是记录锁和间隙锁的组合，确保数据一致性。

MySQL查询性能差的常见原因是什么？Apr 12, 2025 am 12:11 AM

MySQL查询性能不佳的原因主要包括没有使用索引、查询优化器选择错误的执行计划、表设计不合理、数据量过大和锁竞争。 1.没有索引导致查询缓慢，添加索引后可显着提升性能。 2.使用EXPLAIN命令可以分析查询计划，找出优化器错误。 3.重构表结构和优化JOIN条件可改善表设计问题。 4.数据量大时，采用分区和分表策略。 5.高并发环境下，优化事务和锁策略可减少锁竞争。

您什么时候应该使用复合索引与多个单列索引？Apr 11, 2025 am 12:06 AM

在数据库优化中，应根据查询需求选择索引策略：1.当查询涉及多个列且条件顺序固定时，使用复合索引；2.当查询涉及多个列但条件顺序不固定时，使用多个单列索引。复合索引适用于优化多列查询，单列索引则适合单列查询。

如何识别和优化MySQL中的慢速查询？（慢查询日志，performance_schema）Apr 10, 2025 am 09:36 AM

要优化MySQL慢查询，需使用slowquerylog和performance_schema：1.启用slowquerylog并设置阈值，记录慢查询；2.利用performance_schema分析查询执行细节，找出性能瓶颈并优化。

MySQL和SQL：开发人员的基本技能Apr 10, 2025 am 09:30 AM

MySQL和SQL是开发者必备技能。1.MySQL是开源的关系型数据库管理系统，SQL是用于管理和操作数据库的标准语言。2.MySQL通过高效的数据存储和检索功能支持多种存储引擎，SQL通过简单语句完成复杂数据操作。3.使用示例包括基本查询和高级查询，如按条件过滤和排序。4.常见错误包括语法错误和性能问题，可通过检查SQL语句和使用EXPLAIN命令优化。5.性能优化技巧包括使用索引、避免全表扫描、优化JOIN操作和提升代码可读性。

See all articles