mysql安装
官网: www.mysql.com
[root@vm1 ~]# tar xf mysql-boost-5.7.40.tar.gz
[root@vm1 ~]# cd mysql-5.7.40/
[root@vm1 mysql-5.7.40]# yum install -y cmake gcc-c++ bison git libtirpc-devel openssl-devel ncurses-devel rpcgen-1.3.1-4.el8.x86_64.rpm
[root@vm1 mysql-5.7.40]# cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DMYSQL_DATADIR=/data/mysql -DMYSQL_UNIX_ADDR=/data/mysql/mysql.sock -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_EXTRA_CHARSETS=all -DDEFAULT_CHARSET=utf8mb4 -DDEFAULT_COLLATION=utf8mb4_unicode_ci -DWITH_BOOST=/root/mysql-5.7.40/boost/boost_1_59_0
[root@vm1 mysql-5.7.40]# make
[root@vm1 mysql-5.7.40]# make install
mysql初始化
拷贝启动脚本
[root@vm1 mysql-5.7.40]# cd /usr/local/mysql/
[root@vm1 mysql]# cd support-files/
[root@vm1 support-files]# cp mysql.server /etc/init.d/mysqld
修改mysql配置文件
[root@vm1 etc]# vim /etc/my.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/data/mysql
socket=/data/mysql/mysql.sock
初始化数据库
[root@vm1 ~]# mkdir /data/mysql -p
[root@vm1 ~]# useradd -M -d /data/mysql/ -s /sbin/nologin mysql
[root@vm1 ~]# chown mysql.mysql /data/mysql/
[root@vm1 ~]# vim .bash_profile
PATH=$PATH:$HOME/bin:/usr/local/php/bin:/usr/local/mysql/bin
[root@vm1 ~]# source .bash_profile
[root@vm1 ~]# mysqld --initialize --user=mysql
启动服务
[root@vm1 ~]# /etc/init.d/mysqld start
安全初始化
[root@vm1 ~]# mysql_secure_installation
Securing the MySQL server deployment.
Enter password for user root: #输入上面生成的临时密码
The existing password for the user account root has expired. Please set a new password.
New password: #输入两边新密码
Re-enter new password:
VALIDATE PASSWORD PLUGIN can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD plugin?
Press y|Y for Yes, any other key for No: #是否激活密码插件,直接回车
Using existing password for root.
Change the password for root ? ((Press y|Y for Yes, any other key for No) :
#是否再次修改管理员密码,直接回车
... skipping.
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL 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? (Press y|Y for Yes, any other key for No) : y
Success.
#是否删除匿名用户,输入 y
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? (Press y|Y for Yes, any other key for No) : y
Success.
#是否禁用root远程登录,输入 y
By default, MySQL 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? (Press y|Y for Yes, any other key for No) : y
- Dropping test database...
Success.
#是否删除test数据库,输入 y
- 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? (Press y|Y for Yes, any other key for No) : y
Success.
#刷新授权表
All done!
登录数据库
[root@vm1 ~]# mysql -p
部署phpmyadmin
[root@vm1 ~]# unzip phpMyAdmin-5.0.2-all-languages.zip -d /usr/local/nginx/html/
[root@vm1 ~]# cd /usr/local/nginx/html/
[root@vm1 html]# mv phpMyAdmin-5.0.2-all-languages/ mysql
[root@vm1 html]# ls
修改nginx默认首页
[root@vm1 ~]# cd /usr/local/nginx/conf/
[root@vm1 conf]# vim nginx.conf
location / {
root html;
index index.php index.html index.htm;
}
[root@vm1 conf]# nginx -s reload
修改php-fpm配置
[root@vm1 ~]# vim /usr/local/php/etc/php.ini
pdo_mysql.default_socket=/data/mysql/mysql.sock
mysqli.default_socket =/data/mysql/mysql.sock
[root@vm1 ~]# systemctl reload php-fpm
测试访问:192.168.52.130/mysql
输入mysql数据库用户名密码
主从复制
master配置
[root@vm1 ~]# vim /etc/my.cnf
[mysqld]
...
log-bin=mysql-bin
server-id=1
重启服务
[root@vm1 ~]# /etc/init.d/mysqld restart
创建复制用户
[root@vm1 ~]# mysql -p
mysql> CREATE USER 'repl'@'%' IDENTIFIED BY 'westos';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
mysql> show master status;
slave配置
新建数据库实例vm2
[root@vm1 ~]# rsync -a /usr/local/mysql vm2:/usr/local/
[root@vm2 ~]# vim .bash_profile
PATH=$PATH:$HOME/bin:/usr/local/mysql/bin
[root@vm2 ~]# source .bash_profile
[root@vm2 ~]# cd /usr/local/mysql
[root@vm2 mysql]# cp support-files/mysql.server /etc/init.d/mysqld
[root@vm2 mysql]# useradd -M -d /data/mysql/ -s /sbin/nologin mysql
[root@vm2 mysql]# mkdir -p /data/mysql
[root@vm2 mysql]# chown mysql.mysql /data/mysql/
[root@vm2 ~]# vim /etc/my.cnf
[mysqld]
[mysqld]
basedir=/usr/local/mysql
datadir=/data/mysql
socket=/data/mysql/mysql.sock
server-id=2
[root@vm2 mysql]# mysqld --initialize --user=mysql
[root@vm2 mysql]# /etc/init.d/mysqld start
[root@vm2 mysql]# mysql_secure_installation
[root@vm2 mysql]# mysql -p
mysql> CHANGE MASTER TO MASTER_HOST='192.168.52.130', MASTER_USER='repl', MASTER_PASSWORD='westos', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=595;
mysql> start slave;
mysql> show slave status\G;
测试数据同步
master创建测试数据库
mysql> show databases;
mysql> create database test;
mysql> use test;
mysql> create table user(
-> username varchar(25) not null,
-> password varchar(50) not null);
mysql> desc user;
mysql> insert into user values ('user1','123');
mysql> insert into user values ('user2','456');
mysql> select * from user;
slave vm2:
[root@vm2 ~]# mysql -p
mysql> show databases;
mysql> use test;
mysql> show tables;
mysql> select * from user;
再添加一个slave server3
[root@vm1 ~]# rsync -a /usr/local/mysql server3:/usr/local/
[root@vm3 ~]# vim .bash_profile
PATH=$PATH:$HOME/bin:/usr/local/mysql/bin
[root@vm3 ~]# source .bash_profile
[root@vm3 ~]# mkdir -p /data/mysql
[root@vm3 ~]# useradd -M -d /data/mysql/ -s /sbin/nologin mysql
[root@vm3 ~]# chown mysql.mysql /data/mysql/
[root@vm3 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
server-id=3
[root@vm3 ~]# mysqld --initialize --user=mysql
[root@vm3 ~]# cd /usr/local/mysql/
[root@vm3 mysql]# cp support-files/mysql.server /etc/init.d/mysqld
[root@vm3 mysql]# /etc/init.d/mysqld start
[root@vm3 mysql]# mysql_secure_installation
master备份数据库
[root@vm1 ~]# mysqldump -pwestos test > dump.sql
[root@vm1 ~]# scp dump.sql vm3:
注意:
生产环境中备份时需要锁表,保证备份前后的数据一致
mysql> FLUSH TABLES WITH READ LOCK;
备份后再解锁
mysql> UNLOCK TABLES;
注意:
mysqldump命令备份的数据文件,在还原时先DROP TABLE,需要合并数据时需要删除此语句
[root@vm1 ~]# mysql -p
mysql> show master status;
salve vm3还原数据库
[root@vm3 ~]# mysqladmin -pwestos create test
[root@vm3 ~]# mysql -pwestos test < dump.sql
[root@vm3 ~]# mysql -p
mysql> show databases;
mysql> use westos
mysql> select * from user;
mysql> CHANGE MASTER TO MASTER_HOST='192.168.52.130', MASTER_USER='repl', MASTER_PASSWORD='westos', MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=1221;
mysql> start slave;
mysql> show slave status\G;
测试主从同步
[root@vm1 ~]# mysql -p
mysql> insert into test.user values ('user3','666');
mysql> select * from test.user;
[root@vm2 mysql]# mysql -p
mysql> select * from test.user;
[root@vm3 mysql]# mysql -p
mysql> select * from test.user;