生产环境xfsfilesystem上安装Mariadb_MySQL

规划

/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)

p

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

2) 准备数据目录和二进制存放目录

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]>