mysql安装配置

1.安装配置

1.Archlinux

1.安装mariadb

1	pacman -S mariadb

2.初始化数据库

1	mariadb-install-db --user=mysql --basedir=/usr --datadir=/var/lib/mysql

3.启动数据库

1	systemctl start mariadb

4.修改root密码

首次使用root用户登录不需要密码并且只能在本地登录

1	alter user'root'@'localhost' identified by '新密码';

如果要开启远程登录
对于mariadb来说同一个用户在本地登录和在远程登录，密码是互不干扰的

1	GRANT ALL PRIVILEGES ON . TO 'root'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;

2.Centos

1.1.从官网获取

wget https://cdn.mysql.com//Downloads/MySQL-8.0/mysql-community-common-8.0.33-1.el7.x86_64.rpm
wget https://cdn.mysql.com//Downloads/MySQL-8.0/mysql-community-icu-data-files-8.0.33-1.el7.x86_64.rpm
wget https://cdn.mysql.com//Downloads/MySQL-8.0/mysql-community-libs-8.0.33-1.el7.x86_64.rpm
wget https://cdn.mysql.com//Downloads/MySQL-8.0/mysql-community-client-plugins-8.0.33-1.el7.x86_64.rpm
wget https://cdn.mysql.com//Downloads/MySQL-8.0/mysql-community-client-plugins-8.0.33-1.el7.x86_64.rpm

rpm -ivh *.rpm

1.2 从yum源获取

2.安装软件包

1	sudo yum install mysql

2.启动mysql

1 2	sudo systemctl restart mysqld #启动mysql后mysql会自动初始化数据库

3.修改密码

1 2	#获取密码 tail /var/log/mysqld.log

mysq8.0开始默认启用了validate_password组件
如果出现密码问题，可以

#修改密码

#登录mysql（这里的密码通过上面步骤获取）
mysql -uroot -p

#mariadb/mysql (适用于相对较新的版本)
alter user'root'@'localhost' identified by '新密码';

#mariadb（适用于相对较老的版本）
UPDATE mysql.user SET password = PASSWORD('root') WHERE user = 'root';

#刷新
flush privileges

#开启远程登录
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;

如果出现修改密码不符合策略可以卸载

SHOW VARIABLES LIKE 'validate_password%'; #查看密码策略

#查看validate_password plugin的状态
SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'validate%';

#安装或者卸载组件
UNINSTALL COMPONENT 'file://component_validate_password';
INSTALL COMPONENT 'file://component_validate_password';

#mysql默认会加载validate_password component,但是不会加载validate_password plugin
install plugin validate_password soname 'validate_password.so';
UNINSTALL PLUGIN  validate_password;

#修改mysql配置文件
vim /etc/my.cnf
#加入以下两行
validate_password.policy=LOW
validate_password.length=0
validate_password.check_user_name=OFF
#这个用于关闭密码和用户名一样的时候密码不通过。（这些参数依赖与vaildate_password component)

validate_password.number_count + validate_password.special_char_count + (2 * validate_password.mixed_case_count)

2.mysql配置主从复制

2.1 mysql主从复制的原理

1.mysql主库将数据的更改记录记录到二进制日志(binary log)中
2.mysql从库将主库的更改记录的二进制日志同步到本地的中继日志(relay log)
3.mysql从库读取中继日志,根据日志中的操作步骤,同步数据

2.2 配置主库

修改配置文件

[mysqld]
server-id=1
log-bin=localhost-binlog	#日志文件

#需要同步的表，不配置默认同步所有数据库
binlog-do-db=db_a
binlog-do-db=db_b
binlog-do-db=db_c

创建从复制的用户

1 2	#创建slave用户 create user slave@'%' identified with mysql_native_password by 'slave';

2.3 配置从库

修改serverid

1
2
3

[mysqld]
server-id=2
#这个server-id要和主库不一样

修改要复制的数据库的地址以及验证信息

change master to 
master_host='172.17.0.4',
master_port=3306,
master_user='slave',
master_password='slave',
master_log_file='logbin.000002',
master_log_pos=1825;#这个参数可以由主库show master status得到