源码离线安装mariadb
卸载系统自带mariadb-libs
使用下面的命令检查是否安装有MySQL\MariaDB
rpm -qa | grep mariadb
rpm -e mariadb-libs-5.5.68-1.el7.x86_64 --nodeps
添加组和用户
[root@localhost soft]# groupadd -r mysql
[root@localhost soft]# useradd -r -g mysql -s /sbin/nologin -d /usr/local/app/mysql -M mysql
[root@localhost soft]# chown -R mysql:mysql /usr/local/app/mysql/data
编译源代码
使用cmake命令编译
cmake -DCMAKE_INSTALL_PREFIX=/usr/local/app/mysql
-DMYSQL_DATADIR=/usr/local/app/mysql/data
-DSYSCONFDIR=/etc
-DWITH_MYISAM_STORAGE_ENGINE=1
-DWITH_INNOBASE_STORAGE_ENGINE=1
-DWITH_MEMORY_STORAGE_ENGINE=1
-DWITH_READLINE=1
-DMYSQL_UNIX_ADDR=/var/lib/mysql/mysql.sock
-DMYSQL_TCP_PORT=3306
-DENABLED_LOCAL_INFILE=1
-DWITH_PARTITION_STORAGE_ENGINE=1
-DEXTRA_CHARSETS=all
-DDEFAULT_CHARSET=utf8
-DDEFAULT_COLLATION=utf8_general_ci
cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DMYSQL_DATADIR=/home/mysql/data -DSYSCONFDIR=/etc -DWITH_MYISAM_STORAGE_ENGINE=1 -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_MEMORY_STORAGE_ENGINE=1 -DWITH_READLINE=1 -DMYSQL_UNIX_ADDR=/usr/local/mysql/tmp/mysql.sock -DMYSQL_TCP_PORT=3306 -DENABLED_LOCAL_INFILE=1 -DWITH_PARTITION_STORAGE_ENGINE=1 -DEXTRA_CHARSETS=all -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci
注:
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql (指定mariadb 安装的路径)
-DMYSQL_DATADIR=/data/mysql/ (指定mariadb的数据存放路径
-DSYSCONFDIR=/etc (指定mairiadb的配置文件,my.cnf的路径
-DMYSQL_USER=mysql (指定用户为mysql)
-DWITH_INNOBASE_STORAGE_ENGINE=1 (编译INNODB引擎)
-DWITH_ARCHIVE_STORAGE_ENGINE=1 (编译ARCHIVE引擎)
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 (编译BLACKHOLE引擎)
-DWITH_PARTITION_STORAGE_ENGINE=1 (编译PARTITION引擎)
-DWITHOUT_MROONGA_STORAGE_ENGINE=1 (不编译MROONGA引擎)
-DWITH_DEBUG=0 (将Debug Sync工具编译到服务器中)
-DWITH_READLINE=1
-DWITH_SSL=system (要包含的SSL支持类型或要使用的OpenSSL安装的路径名。)
-DWITH_ZLIB=system (编译zlib)
-DWITH_LIBWRAP=0 (Whether to include libwrap (TCP wrappers) support.)
-DENABLED_LOCAL_INFILE=1 (This option controls the compiled-in default LOCAL capability for the MySQL client library)
-DMYSQL_UNIX_ADDR=/data/mysql/mysql.sock (服务器侦听套接字连接的Unix套接字文件路径。这必须是绝对路径名。默认是/tmp/mysql.sock)
-DDEFAULT_CHARSET=utf8 (指定字符集)
-DDEFAULT_COLLATION=utf8_general_ci
编译选项:https://dev.mysql.com/doc/refman/5.7/en/source-configuration-options.html
提示:如果出错,执行rm -f CMakeCache.txt
make && make install
编译过程报错 make[2]: * [sql/CMakeFiles/sql.dir/sql_yacc.cc.o] Error
make[2]: *** [sql/yy_mariadb.cc] 错误 141 make[1]: *** [sql/CMakeFiles/gen_l
安装bison 3.o
安装低版本bison 3.0报错
安装m4包
./configure
make
make install
解决pcre hash值不匹配问题
生成数据库文件
scripts/mysql_install_db --user=mysql --datadir=/usr/local/app/mysql/data
生成数据库文件报错解决办法
scripts/mysql_install_db --datadir=/usr/local/app/mysql/data
--user=mysql 不要加入 --user=mysql参数,会莫名其妙的报错
修改配置文件
将mariadb配置文件copy到/etc目录下
将mysql.server重命名为mysqld,并移动到/etc/init.d/
[root@localhost mysql]# cp support-files/mysql.server /etc/rc.d/init.d/mysqld
chmod +x /etc/init.d/mysqld
my.cnf 配置文件修改
my.cnf权限必须为644
##
## mysqld options _MANDATORY_ for correct opration of the cluster
##
[mysqld]
server-id=1
log_bin=mysql-bin
binlog_format=ROW #开启binlog日志
expire_logs_days=7
log-error=/var/log/mysqld.log
# (This must be substituted by wsrep_format)
# Currently only InnoDB storage engine is supported
default-storage-engine=innodb
sync_binlog = 1 #二进制日志(binary log)同步到磁盘的频率
innodb_flush_log_at_trx_commit = 1 #每次事务提交将日志缓冲区写入log file,并同时flush到磁盘。
配置好脚本文件后使用service命令启动mysqld服务,mysqld是数据库程序的服务名称(一般来讲,服务器上后缀为d的都是daemon进程),再利用chkconfig命令将mysqld加入到开机自启
service mysqld start
chkconfig mysqld on
将日志文件授权给组\用户
chown -R mysql:mysql /var/log/mysqld.log
设置环境变量
打开并新建文件mysql.sh
vim /etc/profile.d/mysql.sh
export PATH=$PATH:/usr/local/app/mysql/bin/
chmod 0777 /etc/profile.d/mysql.sh
source mysql.sh
mysql.sh内容
export PATH=$PATH:/usr/local/app/mysql/bin/
运行MariaDB初始化脚本
[root@localhost mysql]# ./bin/mysql_secure_installation
> 以下提示:
Enter current password for root (enter for none): 输入当前root密码(没有输入)
Set root password? [Y/n] 设置root密码?(是/否)
New password: 输入新root密码
Re-enter new password: 确认输入root密码
Password updated successfully! 密码更新成功
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.
默认情况下,MariaDB安装有一个匿名用户,
允许任何人登录MariaDB而他们无需创建用户帐户。
这个目的是只用于测试,安装去更平缓一些。
你应该进入前删除它们生产环境。
Remove anonymous users? [Y/n] 删除匿名用户?(是/否)
Normally, root should only be allowed to connect from 'localhost'. This
ensures that someone cannot guess at the root password from the network.
通常情况下,root只应允许从localhost连接。
这确保其他用户无法从网络猜测root密码。
Disallow root login remotely? [Y/n] 不允许root登录远程?(是/否)
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.
默认情况下,MariaDB提供了一个名为“测试”的数据库,任何人都可以访问。
这也只用于测试,在进入生产环境之前应该被删除。
Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.
重新加载权限表将确保所有到目前为止所做的更改将立即生效。
Reload privilege tables now? [Y/n] 现在重新加载权限表(是/否)
All done! If you've completed all of the above steps, your MariaDB
installation should now be secure.
全部完成!如果你已经完成了以上步骤,MariaDB安装现在应该安全。
Thanks for using MariaDB!
感谢使用MariaDB!
查看log_bin是否开启
show variables like 'log_bin';
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-cLXyTFR6-1655181828048)(C:\Users\luffy037\OneDrive\Typora图片\image-20211014164744809.png)]
区分表名大小写
lower_case_table_names = 0
不区分表名大小写
lower_case_table_names = 1
索引太长
my.cnf配置中添加
innodb_large_prefix=ON
字段太多
my.cnf配置中添加
innodb_file_per_table
innodb_file_format = Barracuda
innodb_strict_mode = 0
查看所有表
show tables;
查看表结构
use mysql;
describe user;
查看用户和远程主机
select host,user,password from user;
设置远程登录
方式一
update user set host = '%' where user = 'root';
方式二
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY
'root' WITH GRANT OPTION;
用户权限查看
show grants for 'canal'@'%';
1、查看mysql数据库中的所有用户:
mysql> SELECT DISTINCT CONCAT(‘User: ‘’’,user,‘’‘@’‘’,host,‘’‘;’) AS query FROM mysql.user;
2、查看数据库中具体某个用户的权限:
mysql> show grants for ‘root’@‘localhost’;
或者
mysql> select * from mysql.user where user=‘root’ \G;
3、查看user表的表结构。需要具体的项可以根据表的结构来查询。
mysql> desc mysql.user;
4、修改用户权限:
mysql> GRANT ALL ON . TO 用户名
@127.0.0.1
WITH GRANT OPTION;
刷新权限
FLUSH PRIVILEGES;