生产环境 xfs filesystem 上安装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]>