一、源码编译安装mariadb
- 生产环境中，大部分使用的都是二进制安装或者源码编译安装，yum安装很多要求和设置很繁琐，容易出现错误
- 此次源码编译安装需要先安装 cmake
1、准备cmake
[root@Centos6-server~]#rpm -q cmake
package cmake is not installed
[root@Centos6-server~]#yum install cmake
.....
==========================================================================================================================
Package Arch Version Repository Size
==========================================================================================================================
Installing:
cmake x86_64 2.8.12.2-4.el6 base 8.0 M
.....
Installed:
cmake.x86_64 0:2.8.12.2-4.el6
.....
2、清除其他版本的mariadb软件文件
[root@Centos6-server~]#rpm -q mariadb
package mariadb is not installed
[root@Centos6-server~]#rpm -q mariadb-server
package mariadb-server is not installed
[root@Centos6-server~]#find / -name mysql* | xargs rm -rf
3、创建软件用户账号
[root@Centos6-server~]#useradd -r -s /sbin/nologin mysql
[root@Centos6-server~]#id mysql
uid=496(mysql) gid=495(mysql) groups=495(mysql)
4、安装编译安装的包组及准备相关文件
[root@Centos6-server~]#yum groupinstall "development tools"
[root@Centos6-server~]#ls
anaconda-ks.cfg mariadb-10.2.12 mariadb-10.2.12.tar.gz reset68.sh
[root@Centos6-server~]#mkdir /data/mysqldb
[root@Centos6-server~]#chown mysql.mysql /data/mysqldb
[root@Centos6-servermariadb-10.2.12]#ls
appveyor.yml config.h.cmake debian INSTALL-WIN-SOURCE mysql-test README.md strings win
BUILD configure.cmake Docs KNOWN_BUGS.txt mysys scripts support-files wsrep
BUILD-CMAKE COPYING EXCEPTIONS-CLIENT libmariadb mysys_ssl sql tests zlib
client COPYING.thirdparty extra libmysqld pcre sql-bench unittest
cmake CREDITS include libservices plugin sql-common VERSION
CMakeLists.txt dbug INSTALL-SOURCE man randgen storage vio
[root@Centos6-servermariadb-10.2.12]#gcc --version
gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-18)
Copyright (C) 2010 Free Software Foundation, Inc.
This is free software; see the source for copying conditions. There is NO
warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
5、准备安装配置选项
cmake . \
-DCMAKE_INSTALL_PREFIX=/app/mysql \
-DMYSQL_DATADIR=/data/mysqldb/ \
-DSYSCONFDIR=/etc \
-DMYSQL_USER=mysql \
-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=/app/mysql/mysql.sock \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DENABLED_LOCAL_INFILE=1 \
-DWITH_PARTITION_STORAGE_ENGINE=1 \
-DWITH_DEBUG=0 \
-DWITHOUT_MROONGA_STORAGE_ENGINE=1
make && make install
[root@Centos6-servermariadb-10.2.12]#cmake . \
> -DCMAKE_INSTALL_PREFIX=/app/mysql \
> -DMYSQL_DATADIR=/data/mysqldb/ \
> -DSYSCONFDIR=/etc \
> -DMYSQL_USER=mysql \
> -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=/app/mysql/mysql.sock \
> -DDEFAULT_CHARSET=utf8 \
> -DDEFAULT_COLLATION=utf8_general_ci \
> -DENABLED_LOCAL_INFILE=1 \
> -DWITH_PARTITION_STORAGE_ENGINE=1 \
> -DWITH_DEBUG=0 \
> -DWITHOUT_MROONGA_STORAGE_ENGINE=1
......
-- Configuring done
-- Generating done
-- Build files have been written to: /root/mariadb-10.2.12
-- Configuring done
-- Generating done
CMake Warning:
Manually-specified variables were not used by the project:
MYSQL_USER
SYSCONFDIR
WITHOUT_MROONGA_STORAGE_ENGINE
WITH_DEBUG
-- Build files have been written to: /root/mariadb-5.5.59
6、补充其他包
[root@Centos6-servermariadb-10.2.12]#yum install ncurse-devel
[root@Centos6-servermariadb-10.2.12]#yum install gnutls-devel
[root@Centos6-servermariadb-10.2.12]#yum install libevent-devel
[root@Centos6-servermariadb-10.2.12]#yum install "libaio-devel"
[root@Centos6-servermariadb-10.2.12]#yum install "openssl-devel"
[root@Centos6-servermariadb-10.2.12]#yum install libxml2-devel
[root@Centos6-servermariadb-10.2.12]#yum install xz-devel
[root@Centos6-server~]#yum install ncurses-devel
7、调试相关选项
- 添加环境变量
[root@Centos6-server~]#ls /data/mysqldb/
[root@Centos6-server~]#ls /app/mysql/bin/
aria_chk myisam_ftdump mysql_convert_table_format mysql_plugin replace
aria_dump_log myisamlog mysqld mysql_secure_installation resolveip
aria_ftdump myisampack mysqld_multi mysql_setpermission resolve_stack_dump
aria_pack my_print_defaults mysqld_safe mysqlshow wsrep_sst_common
aria_read_log mysql mysqld_safe_helper mysqlslap wsrep_sst_mariabackup
innochecksum mysqlaccess mysqldump mysqltest wsrep_sst_mysqldump
mariabackup mysqladmin mysqldumpslow mysql_tzinfo_to_sql wsrep_sst_rsync
mariadb_config mysqlbinlog mysql_find_rows mysql_upgrade wsrep_sst_xtrabackup
mbstream mysqlcheck mysql_fix_extensions mysql_waitpid wsrep_sst_xtrabackup-v2
msql2mysql mysql_client_test mysqlhotcopy mytop
myisamchk mysql_config mysqlimport perror
[root@Centos6-server~]#echo 'PATH=/app/mysql/bin/:$PATH' > /etc/profile.d/mysql.sh
[root@Centos6-server~]#cat /etc/profile.d/mysql.sh
PATH=/app/mysql/bin/:$PATH
[root@Centos6-server~]#. /etc/profile.d/mysql.sh
[root@Centos6-server~]#/app/mysql/scripts/mysql_install_db --basedir=/app/mysql --datadir=/data/mysqldb/3306/data --user=mysql
#因为我使用的是Cenots6.9安装的10.12，所以报了一些警告！
WARNING: The host 'Centos6-server' could not be looked up with /app/mysql/bin/resolveip.
This probably means that your libc libraries are not 100 % compatible
with this binary MariaDB version. The MariaDB daemon, mysqld, should work
normally with the exception that host name resolving will not work.
This means that you should use IP addresses instead of hostnames
when specifying MariaDB privileges !
Installing MariaDB/MySQL system tables in '/data/mysqldb/3306/data' ...
-------------------------------------------------------------------------------------
警告：无法使用/app/mysql/bin/resolveip查找主机“Centos6-server”。这可能意味着您的libc库与此二进制MariaDB版本不是100％兼容的。 MariaDB守护进程mysqld应该正常工作，但主机名解析不起作用。这意味着你应该使用IP地址而不是主机名
指定MariaDB权限时！在'/data /mysqldb/3306/data'中安装MariaDB /MySQL系统表
-------------------------------------------------------------------------------------
OK
To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system
-------------------------------------------------------------------------------------
要在启动时启动mysqld，必须将support-files/mysql.server复制到系统的正确位置
-------------------------------------------------------------------------------------
PLEASE REMEMBER TO SET A PASSWORD FOR THE MariaDB root USER ! --->请记住为MariaDB root用户设置密码！
To do so, start the server, then issue the following commands:
'/app/mysql/bin/mysqladmin' -u root password 'new-password'
'/app/mysql/bin/mysqladmin' -u root -h Centos6-server password 'new-password'
Alternatively you can run:
'/app/mysql/bin/mysql_secure_installation' --->建议先使用这个脚本，修改密码并删除空用户
which will also give you the option of removing the test
databases and anonymous user created by default. This is
strongly recommended for production servers.
See the MariaDB Knowledgebase at http://mariadb.com/kb or the
MySQL manual for more instructions.
You can start the MariaDB daemon with:
cd '/app/mysql' ; /app/mysql/bin/mysqld_safe --datadir='/data/mysqldb/3306/data'
You can test the MariaDB daemon with mysql-test-run.pl
cd '/app/mysql/mysql-test' ; perl mysql-test-run.pl
Please report any problems at http://mariadb.org/jira
The latest information about MariaDB is available at http://mariadb.org/.
You can find additional information about the MySQL part at:
http://dev.mysql.com
Consider joining MariaDB's strong and vibrant community:
https://mariadb.org/get-involved/
[root@Centos6-server~]#tree -d /data/mysqldb/
/data/mysqldb/
└── 3306
└── data
├── mysql
├── performance_schema
└── test
[root@Centos6-server~]#ls /app/mysql/support-files/ --->选取适合配置文件
binary-configure my-huge.cnf my-large.cnf my-small.cnf mysql-log-rotate policy wsrep_notify
magic my-innodb-heavy-4G.cnf my-medium.cnf mysqld_multi.server mysql.server wsrep.cnf
[root@Centos6-server~]#cp /app/mysql/support-files/my-large.cnf /etc/my.cnf
cp: overwrite `/etc/my.cnf'? y
[root@Centos6-server~]#vim /etc/my.cnf --->修改配置文件
[mysqld]
datadir=/data/mysqldb/3306/data
[root@Centos6-server~]#cp /app/mysql/support-files/mysql.server /etc/init.d/mysqld --->用给定的模板当作启动配置文件
[root@Centos6-server~]#vim /etc/init.d/mysqld
[root@Centos6-server~]#chkconfig --list mysqld
service mysqld supports chkconfig, but is not referenced in any runlevel (run 'chkconfig --add mysqld')
[root@Centos6-server~]#chkconfig --add mysqld --->加入启动服务
[root@Centos6-server~]#chkconfig --list mysqld
mysqld 0:off 1:off 2:on 3:on 4:on 5:on 6:off
[root@Centos6-server~]#chown -R mysql.mysql /data/mysqldb/3306/ --->修改数据库所属主组
[root@Centos6-server~]#ll -d /data/mysqldb/3306/
drwxr-xr-x 3 mysql mysql 4096 Jan 23 02:29 /data/mysqldb/3306/
[root@Centos6-server~]#chmod -R 700 /data/mysqldb/ --->修改数据库权限
[root@Centos6-server~]#ll -d /data/mysqldb/3306/
drwx------ 3 700 mysql 4096 Jan 23 02:29 /data/mysqldb/3306/
[root@Centos6-server~]#ll /app/mysql/ -d --->给mysql在 /app/mysql添加属组
drwxr-xr-x 12 root root 4096 Jan 23 02:21 /app/mysql/
[root@Centos6-server~]#chgrp -R mysql /app/mysql/ --->给mysql在 /app/mysql添加写权限
[root@Centos6-server~]#chmod 775 /app/mysql/
[root@Centos6-server~]#ll /app/mysql/ -d
drwxrwxr-x 12 root mysql 4096 Jan 23 02:21 /app/mysql/
[root@Centos6-server~]#service mysqld start --->启动成功
Starting MySQL.180123 18:54:29 mysqld_safe Logging to '/data/mysqldb/3306/data/Centos6-server.err'.
180123 18:54:29 mysqld_safe Starting mysqld daemon with databases from /data/mysqldb/3306/data
.. SUCCESS!
二、教材二进制编译过程
- (1) 准备用户
groupadd -r -g 306 mysql
useradd -r -g 306 -u 306 –m –d /app/data mysql - (2) 准备数据目录
以/app/data为例,建议使用逻辑卷
chown mysql:mysql /app/data - (3) 准备二进制程序
tar xf mariadb-VERSION-linux-x86_64.tar.gz -C /usr/local
cd /usr/local;ln -sv mariadb-VERSION mysql
chown -R root:mysql /usr/local/mysql/ - (4) 准备配置文件
配置格式：类ini格式，各程序由单个配置文件提供配[prog_name]
配置文件查找次序：后面覆盖前面的配置文件
/etc/my.cnf -->/etc/mysql/my.cnf --> --default-extra-file=/PATH/TO/CONF_FILE --> ~/.my.cnf
mkdir /etc/mysql/
cp support-files/my-large.cnf /etc/mysql/my.cnf
[mysqld]中添加三个选项：
datadir = /app/data
innodb_file_per_table = on
skip_name_resolve = on 禁止主机名解析，建议使用 - (5)创建数据库文件
cd /usr/local/mysql/
./scripts/mysql_install_db --datadir=/app/data --user=mysql - (6)准备日志文件
touch /var/log/mysqld.log
chown mysqld /var/log/mysqld.log - (7)准备服务脚本，并启动服务
cp ./support-files/mysql.server /etc/rc.d/init.d/mysqld
chkconfig --add mysqld
service mysqld start - (8)安全初始化
/user/local/mysql/bin/mysql_secure_installation
#给数据单独分区
[root@centos7mini-2~]#fdisk /dev/sda
Welcome to fdisk (util-linux 2.23.2).
Changes will remain in memory only, until you decide to write them.
Be careful before using the write command.
Command (m for help): p
Device Boot Start End Blocks Id System
/dev/sda1 * 2048 4196351 2097152 83 Linux
/dev/sda2 4196352 109053951 52428800 83 Linux
/dev/sda3 109053952 150996991 20971520 83 Linux
/dev/sda4 150996992 209715199 29359104 5 Extended
/dev/sda5 150999040 155193343 2097152 82 Linux swap / Solaris
Command (m for help): n
All primary partitions are in use
Adding logical partition 6
First sector (155195392-209715199, default 155195392):
Using default value 155195392
Last sector, +sectors or +size{K,M,G} (155195392-209715199, default 209715199): +10G
Partition 6 of type Linux and of size 10 GiB is set
Command (m for help): t
Partition number (1-6, default 6):
Hex code (type L to list all codes): 8e
Changed type of partition 'Linux' to 'Linux LVM'
Command (m for help): p
Disk /dev/sda: 107.4 GB, 107374182400 bytes, 209715200 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk label type: dos
Disk identifier: 0x000d3aae
Device Boot Start End Blocks Id System
/dev/sda1 * 2048 4196351 2097152 83 Linux
/dev/sda2 4196352 109053951 52428800 83 Linux
/dev/sda3 109053952 150996991 20971520 83 Linux
/dev/sda4 150996992 209715199 29359104 5 Extended
/dev/sda5 150999040 155193343 2097152 82 Linux swap / Solaris
/dev/sda6 155195392 176166911 10485760 8e Linux LVM
Command (m for help): w
The partition table has been altered!
Calling ioctl() to re-read partition table.
WARNING: Re-reading the partition table failed with error 16: Device or resource busy.
The kernel still uses the old table. The new table will be used at
the next reboot or after you run partprobe(8) or kpartx(8)
Syncing disks.
[root@centos7mini-2~]#partprobe
Warning: Unable to open /dev/sr0 read-write (Read-only file system). /dev/sr0 has been opened read-only.
[root@centos7mini-2~]#fdisk -l
Disk /dev/sda: 107.4 GB, 107374182400 bytes, 209715200 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk label type: dos
Disk identifier: 0x000d3aae
Device Boot Start End Blocks Id System
/dev/sda1 * 2048 4196351 2097152 83 Linux
/dev/sda2 4196352 109053951 52428800 83 Linux
/dev/sda3 109053952 150996991 20971520 83 Linux
/dev/sda4 150996992 209715199 29359104 5 Extended
/dev/sda5 150999040 155193343 2097152 82 Linux swap / Solaris
/dev/sda6 155195392 176166911 10485760 8e Linux LVM
#创建逻辑卷
[root@centos7mini-2~]#pvcreate /dev/sda6
Physical volume "/dev/sda6" successfully created.
[root@centos7mini-2~]#pvs
PV VG Fmt Attr PSize PFree
/dev/sda6 lvm2 --- 10.00g 10.00g
[root@centos7mini-2~]#vgcreate mysqldata /dev/sda6 -s 4M
Volume group "mysqldata" successfully created
[root@centos7mini-2~]#vgs
VG #PV #LV #SN Attr VSize VFree
mysqldata 1 0 0 wz--n- <10.00g <10.00g
[root@centos7mini-2~]#vgdisplay
--- Volume group ---
VG Name mysqldata
System ID
Format lvm2
Metadata Areas 1
Metadata Sequence No 1
VG Access read/write
VG Status resizable
MAX LV 0
Cur LV 0
Open LV 0
Max PV 0
Cur PV 1
Act PV 1
VG Size <10.00 GiB
PE Size 4.00 MiB
Total PE 2559
Alloc PE / Size 0 / 0
Free PE / Size 2559 / <10.00 GiB
VG UUID FTJuZE-BVbH-qAup-woFM-El6Y-p4Rv-wb2Qgw
[root@centos7mini-2~]#lvcreate -n lv_mysqldata -l 100%FREE mysqldata
Logical volume "lv_mysqldata" created.
[root@centos7mini-2~]#lvs
LV VG Attr LSize Pool Origin Data% Meta% Move Log Cpy%Sync Convert
lv_mysqldata mysqldata -wi-a----- <10.00g
[root@centos7mini-2~]#lvdisplay
--- Logical volume ---
LV Path /dev/mysqldata/lv_mysqldata
LV Name lv_mysqldata
VG Name mysqldata
LV UUID Ht2RuM-1qAW-VOPJ-GO2w-2r1I-p2Oi-IJh6WH
LV Write Access read/write
LV Creation host, time centos7mini-2, 2018-02-19 13:45:24 +0800
LV Status available
# open 0
LV Size <10.00 GiB
Current LE 2559
Segments 1
Allocation inherit
Read ahead sectors auto
- currently set to 8192
Block device 253:0
#创建文件系统
[root@centos7mini-2~]#mkfs.xfs /dev/mysqldata/lv_mysqldata
meta-data=/dev/mysqldata/lv_mysqldata isize=512 agcount=4, agsize=655104 blks
= sectsz=512 attr=2, projid32bit=1
= crc=1 finobt=0, sparse=0
data = bsize=4096 blocks=2620416, imaxpct=25
= sunit=0 swidth=0 blks
naming =version 2 bsize=4096 ascii-ci=0 ftype=1
log =internal log bsize=4096 blocks=2560, version=2
= sectsz=512 sunit=0 blks, lazy-count=1
realtime =none extsz=4096 blocks=0, rtextents=0\
[root@centos7mini-2~]#mkdir /data -pv
[root@centos7mini-2/]#vim /etc/fstab
UUID=2fb078aa-57e5-43a1-aefe-eeb792bff3ef /data xfs defaults 0 0
#挂载
[root@centos7mini-2/]#mount -a
[root@centos7mini-2/]#df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/mysqldata-lv_mysqldata 10G 33M 10G 1% /data
[root@centos7mini-2/]#chown -R mysql.mysql /data
[root@centos7mini-2/]#chmod 770 /data
[root@centos7mini-2/]#mkdir /data/mysqldb
#创建默认数据库相关内容
[root@centos7mini-2mysql]#pwd
/usr/local/mysql
[root@centos7mini-2mysql]#scripts/mysql_install_db --datadir=/data/mysqldb --user=mysql
#注意：必须在这个路径下执行这条命令
Installing MariaDB/MySQL system tables in '/data/mysqldb' ...
2018-02-19 14:09:45 139791179409216 [Warning] 'THREAD_CONCURRENCY' is deprecated and will be removed in a future release.
OK
To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system
PLEASE REMEMBER TO SET A PASSWORD FOR THE MariaDB root USER !
To do so, start the server, then issue the following commands:
'./bin/mysqladmin' -u root password 'new-password'
'./bin/mysqladmin' -u root -h centos7mini-2 password 'new-password'
Alternatively you can run:
'./bin/mysql_secure_installation'
which will also give you the option of removing the test
databases and anonymous user created by default. This is
strongly recommended for production servers.
See the MariaDB Knowledgebase at http://mariadb.com/kb or the
MySQL manual for more instructions.
You can start the MariaDB daemon with:
cd '.' ; ./bin/mysqld_safe --datadir='/data/mysqldb'
You can test the MariaDB daemon with mysql-test-run.pl
cd './mysql-test' ; perl mysql-test-run.pl
Please report any problems at http://mariadb.org/jira
The latest information about MariaDB is available at http://mariadb.org/.
You can find additional information about the MySQL part at:
http://dev.mysql.com
Consider joining MariaDB's strong and vibrant community:
https://mariadb.org/get-involved/
#添加配置文件
[root@centos7mini-2~]#cp /usr/local/mysql/files/my-huge.cnf /etc/my.cnf
[root@centos7mini-2~]#vim /etc/my.cnf
[mysqld]
datadir=/data/mysqldb
port = 3306
#修改服务启动脚本
[root@centos7mini-2~]#cp /usr/local/mysql/files/mysql.server /etc/init.d/mysqld
[root@centos7mini-2~]#chkconfig --add mysqld
[root@centos7mini-2mysql]#chkconfig --list
mysqld 0:off 1:off 2:on 3:on 4:on 5:on 6:off
[root@centos7mini-2mysql]#chkconfig mysqld on
#运行一次安全脚本
[root@centos7mini-2~]#mysql_secure_installation
NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!
In order to log into MariaDB to secure it, we'll need the current
password for the root user. If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.
Enter current password for root (enter for none):
OK, successfully used password, moving on...
Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.
Set root password? [Y/n] y #设置root口令
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
... Success!
By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them. This is intended only for testing, and to make the installation
go a bit smoother. You should remove them before moving into a
production environment.
Remove anonymous users? [Y/n] y #删除匿名用户
... Success!
Normally, root should only be allowed to connect from 'localhost'. This
ensures that someone cannot guess at the root password from the network.
Disallow root login remotely? [Y/n] n #是否禁用远程root连接
... skipping.
By default, MariaDB comes with a database named 'test' that anyone can
access. This is also intended only for testing, and should be removed
before moving into a production environment.
Remove test database and access to it? [Y/n] y #删除test库
- Dropping test database...
... Success!
- Removing privileges on test database...
... Success!
Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.
Reload privilege tables now? [Y/n] y #是否加载权限表
... Success!
Cleaning up...
All done! If you've completed all of the above steps, your MariaDB
installation should now be secure.
Thanks for using MariaDB!
三、实现多实例
1、准备多实例数据库
[root@Centos6-server~]#mkdir /data/mysqldb/{3306,3307,3308}/{pid,log,etc,data} -pv
[root@Centos6-server~]#/app/mysql/scripts/mysql_install_db --basedir=/app/mysql --datadir=/data/mysqldb/3307/data --user=mysql
WARNING: The host 'Centos6-server' could not be looked up with /app/mysql/bin/resolveip.
This probably means that your libc libraries are not 100 % compatible
with this binary MariaDB version. The MariaDB daemon, mysqld, should work
normally with the exception that host name resolving will not work.
This means that you should use IP addresses instead of hostnames
when specifying MariaDB privileges !
Installing MariaDB/MySQL system tables in '/data/mysqldb/3307/data' ...
180123 18:59:25 [Warning] 'THREAD_CONCURRENCY' is deprecated and will be removed in a future release. --->'THREAD_CONCURRENCY'已被弃用，并将在未来版本中删除
180123 18:59:25 [Note] /app/mysql/bin/mysqld (mysqld 5.5.59-MariaDB) starting as process 20253 ... --->作为进程20253开始
OK
Filling help tables...
180123 18:59:26 [Warning] 'THREAD_CONCURRENCY' is deprecated and will be removed in a future release.
180123 18:59:26 [Note] /app/mysql/bin/mysqld (mysqld 5.5.59-MariaDB) starting as process 20262 ...
OK
[root@Centos6-server~]#/app/mysql/scripts/mysql_install_db --basedir=/app/mysql --datadir=/data/mysqldb/3308/data --user=mysql
.....
[root@Centos6-server~]#tree -d /data/mysqldb
/data/mysqldb/
├── 3306
│ ├── data
│ │ ├── mysql
│ │ ├── performance_schema
│ │ └── test
│ ├── etc
│ ├── log
│ └── pid
├── 3307
│ ├── data
│ │ ├── mysql
│ │ ├── performance_schema
│ │ └── test
│ ├── etc
│ ├── log
│ └── pid
└── 3308
├── data
│ ├── mysql
│ ├── performance_schema
│ └── test
├── etc
├── log
└── pid/data/mysqldb
[root@Centos6-server~]#vim /data/mysqldb/3307/etc/my.cnf
.....
[mysqld] --->修改端口号
datadir=/data/mysqldb/3307/data
port=3307
[mysql_safe] --->添加的日志和pid路径
log-error=/data/mysqldb/3307/log/mariadb.log
pid-file=/mysqldb/3307/pid/mariadb.pid
[root@Centos6-server3307]#./mysqld start
Starting MySQL...
[root@Centos6-server3308]#./mysqld start
Starting MySQL...
[root@Centos6-server3307]#ss -ntlp
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 50 *:3306 *:* users:(("mysqld",20163,15))
LISTEN 0 50 *:3307 *:* users:(("mysqld",21976,15))
LISTEN 0 50 *:3308 *:* users:(("mysqld",21662,15))
[root@Centos6-server3307]#vim mysqld
#!/bin/bash
port=3307
mysql_user="root"
mysql_pwd="centos"
cmd_path="/app/mysql/bin"
mysql_basedir="/data/mysqldb"
mysql_sock="${mysql_basedir}/${prot}/mysql.sock"
function_start_mysql()
{
if [ ! -e "$mysql_sock" ] ; then
printf "^[[1;32m Starting MySQL...^[[0m \n"
${cmd_path}/mysqld_safe --defaults-file=${mysql_basedir}/{port}/etc/my.cnf &> /dev/null &
else |--->启动服务配置文件的路径，配置文件内有启动命令
printf "^[[1;5;33m MySQL is running...^[[0m \n"
exit
fi
}
function_stop_mysql()
{
if [ ! -e "$mysql_sock" ] ; then
printf "^[[1;5;33m MySQL is stopped...^[[0m \n"
exit
else
printf "^[[1;31m Stoping MySQL...^[[0m \n"
${cmd_path}/mysqladmin -u ${mysql_user } -p${mysql_pwd} -S ${mysql_sock} shutdown
fi |--->关闭数据库专用语句，不要用kill关闭，容易导致数据丢失
} |--->并且建议加上密码才能关闭，防止他人误操作
function_restart_mysql()
{
printf "^[[1;36m Restarting MySQL...^[[0m \n"
function_stop_mysql
sleep 2
function_start_mysql
}
case $ in
start)
function_start_mysql
;;
stop)
function_stop_mysql
;;
restart)
function_restart_mysql
;;
*)
printf "Usage: ${mysql_basedir}/${prot}/bing/mysqld {start|stop|restart}\n"
esac
[root@Centos6-server3308]#vim mysqld
#!/bin/bash
port=3308
mysql_user="root"
mysql_pwd="centos"
cmd_path="/app/mysql/bin"
mysql_basedir="/data/mysqldb"
mysql_sock="${mysql_basedir}/${port}/mysql.sock"
function_start_mysql()
{
if [ ! -e "$mysql_sock" ] ; then
pritnf "^[[1;32m Starting MySQL...^[[0m \n"
${cmd_path}/mysqld_safe --defaults-file=${mysql_basedir}/${port}/etc/my.cnf &> /dev/null &
else
printf "^[[1;5;33m MySQL is running...^[[0m \n"
exit
fi
}
function_stop_mysql()
{
if [ ! -e "$mysql_sock" ] ; then
printf "^[[1;5;33m MySQL is stopped...^[[0m \n"
exit
else
printf "^[[1;31m Stoping MySQL...^[[0m \n"
${cmd_path}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S ${mysql_sock} shutdown
fi
}
function_restart_mysql()
{
printf "^[[1;36m Restarting MySQL...^[[0m \n"
function_stop_mysql
sleep 2
function_start_mysql
}
case $1 in
start)
function_start_mysql
;;
stop)
function_stop_mysql
;;
restart)
function_restart_mysql
;;
*)
pritnf "Usage: ${mysql_basedir}/${port}/bin/mysqld {start|stop|restart}\n"
esca
2、配置文件内启动过程
[root@Centos6-server~]#vim /etc/my.cnf
.....
$exec --datadir="$datadir" --socket="$socketfile"\
--pid-file="mypidfile" \
--basedir=/usr --user=mysql > /dev/null 2>&1 &
safe_pid=$!
3、配置mysql服务密码及删除空用户
- 生成数据库时，提示修改密码及删除空用户
PLEASE REMEMBER TO SET A PASSWORD FOR THE MariaDB root USER ! --->请记住为MariaDB root用户设置密码！
To do so, start the server, then issue the following commands:
'/app/mysql/bin/mysqladmin' -u root password 'new-password'
'/app/mysql/bin/mysqladmin' -u root -h Centos6-server password 'new-password'
Alternatively you can run:
'/app/mysql/bin/mysql_secure_installation' --->建议先使用这个脚本，修改密码并删除空用户
- 使用命令修改密码
#在centos命令行加密码
[root@Centos6-server~]#mysqladmin -uroot -S /data/mysqldb/3308/mysql.sock password 'centos'
[root@Centos6-server~]#/data/mysqldb/3308/mysqld stop
Stoping MySQL...
[root@Centos6-server~]#/data/mysqldb/3308/mysqld start
Starting MySQL...
#在mysql里面修改密码，下面登陆的就是使用空用户及空口令登陆的
[root@Centos6-server~]#mysql -S /data/mysqldb/3307/mysql.sock
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 1
Server version: 5.5.59-MariaDB Source distribution
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> select user,password,host from mysql.user;
+------+----------+----------------+
| user | password | host |
+------+----------+----------------+
| root | | localhost |
| root | | centos6-server |
| root | | 127.0.0.1 |
| root | | ::1 |
| | | localhost |
| | | centos6-server |
+------+----------+----------------+
6 rows in set (0.01 sec)
#使用update命令，修改root账号密码，使用了password（）函数，生成加密口令
MariaDB [(none)]> update mysql.user set password=password("centos") where user='root';
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4 Changed: 4 Warnings: 0
MariaDB [(none)]> select user,password,host from mysql.user;
+------+-------------------------------------------+----------------+
| user | password | host |
+------+-------------------------------------------+----------------+
| root | *128977E278358FF80A246B5046F51043A2B1FCED | localhost |
| root | *128977E278358FF80A246B5046F51043A2B1FCED | centos6-server |
| root | *128977E278358FF80A246B5046F51043A2B1FCED | 127.0.0.1 |
| root | *128977E278358FF80A246B5046F51043A2B1FCED | ::1 |
| | | localhost |
| | | centos6-server |
+------+-------------------------------------------+----------------+
6 rows in set (0.00 sec)
#使用dorp命令，删除空用户
MariaDB [(none)]> drop user ''@'localhost e;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> drop user ''@'centos6-server';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> drop user '@'::1';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> drop user centos6-server';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> select user,password,host from mysql.user ;
+------+-------------------------------------------+----------------+
| user | password | host |
+------+-------------------------------------------+----------------+
| root | *128977E278358FF80A246B5046F51043A2B1FCED | localhost |
| root | *128977E278358FF80A246B5046F51043A2B1FCED | 127.0.0.1 |
+------+-------------------------------------------+----------------+
2 rows in set (0.00 sec)
#修改后，要刷新，才能生效
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> quit
Bye
[root@Centos6-server~]#mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 5.5.59-MariaDB Source distribution
.....
MariaDB [(none)]> show variables like 'port' ;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port | 3306 |
+---------------+-------+
1 row in set (0.01 sec)
4、yum安装实现多实例
- yum可以指定源地址，安装特定的版本
- 安装好后，建立多实力的目录，以存放相关数据及配置文件
- 最好使用逻辑卷进行存储，这样后期方便扩容，单独分区磁盘，没有办法进行扩容
[root@centos7mini-2~]#mkdir /mysqldb/{3306,3307,3308} -pv
[root@centos7mini-2~]#mkdir /mysqldb/{3306,3307,3308}/{etc,socket,pid,log,data} -pv
[root@centos7mini-2~]#chown -R mysql.mysql /mysqldb/
#注意：如果是源码安装，还需要指定--basedir
[root@centos7mini-2~]#mysql_install_db --datadir=/mysqldb/3306/data --user=mysql --basedir=usr
[root@centos7mini-2~]#mysql_install_db --datadir=/mysqldb/3307/data --user=mysql --basedir=usr
[root@centos7mini-2~]#mysql_install_db --datadir=/mysqldb/3308/data --user=mysql --basedir=usr
#制作多实例配置文件
[root@centos7mini-2~]#cp /etc/my.cnf /mysqldb/3306/etc
[root@centos7mini-2~]#vim /mysqldb/3306/etc/my.cnf
[mysqld]
port=3306 #默认3306是不需要加的，但是多实例其他的需要加上，例如3307
datadir=/mysqldb/3306/data
socket=/mysqldb/3306/socket/mysql.sock
[mysqld_safe]
log-error=/mysqldb/3306/log/mariadb.log
pid-file==/mysqldb/3306/pid/mariadb.pid
[root@centos7mini-2~]#cp /mysqldb/3306/etc/my.cnf /mysqldb/3307/etc/my.cnf
[root@centos7mini-2~]#cp /mysqldb/3306/etc/my.cnf /mysqldb/3308/etc/my.cnf
[root@centos7mini-2~]#sed -i 's/3306/3307/' /mysqldb/3307/etc/my.cnf
[root@centos7mini-2~]#sed -i 's/3306/3308/' /mysqldb/3307/etc/my.cnf
#修改启动脚本
[root@centos7mini-2~]#vim /mysqldb/3306/mysqld
#!/bin/bash
port=3306
mysql_user="root"
mysql_pwd="centos"
cmd_path="/usr/bin"
mysql_basedir="/mysqldb"
mysql_sock="${mysql_basedir}/${port}/socket/mysql.sock"
function_start_mysql()
{
if [ ! -e "$mysql_sock" ] ; then
pritnf "^[[1;32m Starting MySQL...^[[0m \n"
${cmd_path}/mysqld_safe --defaults-file=${mysql_basedir}/${port}/etc/my.cnf &> /dev/null &
else
printf "^[[1;5;33m MySQL is running...^[[0m \n"
exit
fi
}
function_stop_mysql()
{
if [ ! -e "$mysql_sock" ] ; then
printf "^[[1;5;33m MySQL is stopped...^[[0m \n"
exit
else
printf "^[[1;31m Stoping MySQL...^[[0m \n"
${cmd_path}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S ${mysql_sock} shutdown
fi
}
function_restart_mysql()
{
printf "^[[1;36m Restarting MySQL...^[[0m \n"
function_stop_mysql
sleep 2
function_start_mysql
}
case $1 in
start)
function_start_mysql
;;
stop)
function_stop_mysql
;;
restart)
function_restart_mysql
;;
*)
pritnf "Usage: ${mysql_basedir}/${port}/bin/mysqld {start|stop|restart}\n"
esca
[root@centos7mini-2~]#cp /mysqldb/3306/mysqld /mysqldb/3307/mysqld
[root@centos7mini-2~]#chmod 700 /mysqldb/3306/mysqld
[root@centos7mini 3306]#./mysqld start
[root@centos7mini-2~]#mysql -S /mysqldb/3307/socket/mysql.sock
MariaDB [(none)]> show variables like 'port';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port | 3307 |
+---------------+-------+
1 row in set (0.00 sec)
5、修改多实例数据库登陆口令
[root@centos7mini~]#mysqladmin -uroot -S /mysqldb/3307/socket/mysql.sock password 'centos'
https://blog.51cto.com/exia00linux/2069489
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
