MySQL数据库（231010）

最新推荐文章于 2024-11-14 18:36:14 发布

Hongx06

最新推荐文章于 2024-11-14 18:36:14 发布

阅读量34

点赞数

文章标签：数据库 mysql

本文链接：https://blog.csdn.net/m0_64028800/article/details/133775975

版权

mysql安装

tar xf mysql-boost-5.7.40.tar.gz
cd 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

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

make
make install

mysql初始化

拷贝启动脚本

cd /usr/local/mysql/
cd support-files/
cp mysql.server /etc/init.d/mysqld

修改mysql配置文件

vim /etc/my.cnf

[mysqld]
basedir=/usr/local/mysql
datadir=/data/mysql
socket=/data/mysql/mysql.sock

初始化数据库

mkdir /data/mysql -p
useradd -M -d /data/mysql/ -s /sbin/nologin mysql
chown mysql.mysql /data/mysql/

 vim .bash_profile

# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/bin:/usr/local/php/bin:/usr/local/mysql/bin
export PATH


source .bash_profile

mysqld --initialize --user=mysql        //初始化数据库

/etc/init.d/mysqld start        //启动服务

mysql_secure_installation            //安全性设置

输入两边上面生成的临时密码

是否激活密码插件    直接回车
是否再次修改管理员密码   直接回车
是否删除匿名用户    y
是否禁用root远程登录    y
是否删除test数据库        y
刷新授权表        y

登录数据库

部署phpmyadmin

phpMyAdmin是一款基于web界面的MySQL数据库管理工具。

unzip phpMyAdmin-5.0.2-all-languages.zip -d /usr/local/nginx/html/
cd /usr/local/nginx/html/
mv phpMyAdmin-5.0.2-all-languages/ phpmyadmin

修改nginx默认首页

vim /usr/local/nginx/conf/nginx.conf

location / {
            root   html;
            index  index.php index.html index.htm;
        }

修改php-fpm配置

vim /usr/local/php/etc/php.ini

mysqli.default_socket = /data/mysql/mysql.sock
pdo_mysql.default_socket=/data/mysql/mysql.sock

systemctl reload php-fpm

测试访问，输入mysql用户名和密码

主从复制

master配置

vim /etc/my.cnf

[mysqld]
...
log-bin=mysql-bin
server-id=1

/etc/init.d/mysqld restart

创建复制用户

mysql -pwestos

CREATE USER 'repl'@'%' IDENTIFIED BY 'westos';    //创建用户repl，密码为westos，可从任何IP连接到mysql
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';     //给用户REPLICATION SLAVE权限  

SHOW MASTER STATUS;

salve配置

配置server2

server1

rsync -a /usr/local/mysql server2:/usr/local/

server2

vim .bash_profile

PATH=$PATH:$HOME/bin:/usr/local/mysql/bin

source .bash_profile

cd /usr/local/mysql
cp support-files/mysql.server /etc/init.d/mysqld
useradd -M -d /data/mysql/ -s /sbin/nologin mysql
mkdir -p /data/mysql
chown mysql.mysql /data/mysql/
vim /etc/my.cnf

[mysqld]
basedir=/usr/local/mysql
datadir=/data/mysql
socket=/data/mysql/mysql.sock

server-id=2


mysqld --initialize --user=mysql
/etc/init.d/mysqld start
mysql_secure_installation

mysql -pwestos

CHANGE MASTER TO MASTER_HOST='192.168.81.30', MASTER_USER='repl', MASTER_PASSWORD='westos', MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=319;

start slave;
show slave status\G;

在master创建数据，测试数据同步

再添加一个slave server3

vim /etc/my.cnf

[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock

server-id=3

其余同server2

master备份数据库

server1

mysqldump -pwestos userdb > dump.sql
scp dump.sql server3;

注意：

生产环境中备份时需要锁表，保证备份前后的数据一致

mysql> FLUSH TABLES WITH READ LOCK;

备份后再解锁

mysql> UNLOCK TABLES;

注意：

mysqldump命令备份的数据文件，在还原时先DROP TABLE，需要合并数据时需要删除此语句

mysql -pwestos
show master status;

salve server3还原数据库

server3

mysqladmin -pwestos create userdb
mysql -pwestos userdb < dump.sql

CHANGE MASTER TO MASTER_HOST='192.168.81.30', MASTER_USER='repl', MASTER_PASSWORD='westos', MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=795;
start slave;
show slave status\G;

在server1添加数据，测试主从同步

server1