基于centos7.9搭建mysql 8.0.28 主从模式并进行版本升级

  • 发表于：
  • 分类： 网络资源
  • 浏览：浏览(50)

目录

❤ 前言

 需要搭建一套mysql 8.0.28 版本的数据库，升级到MySQL 8.0.34
mysql下载地址
mysql版本情况
mysql文档

❤ 一、环境

操作系统:centos 7.9(2009)
下载的mysql的包：mysql-8.0.28-linux-glibc2.12-x86_64.tar

操作系统的glibc是2.17的，下载的数据库版本是2.12的
[root@master ~]# rpm -aq|grep glibc
glibc-2.17-317.el7.x86_64
glibc-common-2.17-317.el7.x86_64

☀1、根分区进行扩容

 由于实验环境以前分配的空间是10G,已经不够了，需要扩容。

[root@master ~]# fdisk /dev/sda
Command (m for help): n
Partition type:
   p   primary (2 primary, 0 extended, 2 free)
   e   extended
Select (default p): p
Partition number (3,4, default 3): 
First sector (20971520-62914559, default 20971520): 
Using default value 20971520
Last sector, +sectors or +size{K,M,G} (20971520-62914559, default 62914559): 
Using default value 62914559
Partition 3 of type Linux and of size 20 GiB is set
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: 32.2 GB, 32212254720 bytes, 62914560 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: 0x000e0f5b

   Device Boot      Start         End      Blocks   Id  System
/dev/sda1   *        2048     2099199     1048576   83  Linux
/dev/sda2         2099200    20971519     9436160   8e  Linux LVM
/dev/sda3        20971520    62914559    20971520   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.
  • 执行命令
partprobe
  • 创建PV
[root@master ~]# pvcreate /dev/sda3
  Physical volume "/dev/sda3" successfully created.
  • 查询vg信息
[root@master ~]# vgdisplay 
  --- Volume group ---
  VG Name               centos
  System ID             
  Format                lvm2
  Metadata Areas        1
  Metadata Sequence No  3
  VG Access             read/write
  VG Status             resizable
  MAX LV                0
  Cur LV                2
  Open LV               2
  Max PV                0
  Cur PV                1
  Act PV                1
  VG Size               <9.00 GiB
  PE Size               4.00 MiB
  Total PE              2303
  Alloc PE / Size       2303 / <9.00 GiB
  Free  PE / Size       0 / 0   
  VG UUID               v2nMSm-xUhk-W82l-amgX-u36i-wsjN-IHxuit
  • 查询pv信息
[root@master ~]# pvdisplay 
  --- Physical volume ---
  PV Name               /dev/sda2
  VG Name               centos
  PV Size               <9.00 GiB / not usable 3.00 MiB
  Allocatable           yes (but full)
  PE Size               4.00 MiB
  Total PE              2303
  Free PE               0
  Allocated PE          2303
  PV UUID               Rajrpd-afyc-EEjh-OFCi-xIGy-hJQ0-KedE3o
   
  "/dev/sda3" is a new physical volume of "20.00 GiB"
  --- NEW Physical volume ---
  PV Name               /dev/sda3
  VG Name               
  PV Size               20.00 GiB
  Allocatable           NO
  PE Size               0   
  Total PE              0
  Free PE               0
  Allocated PE          0
  PV UUID               O0r98J-Y4Gk-HE5S-m5aj-rv9p-5LEk-9S5PHn
  • 把/dev/sda3 添加到vg centos中
[root@master ~]# vgextend centos /dev/sda3
  Volume group "centos" successfully extended
  • 通过查询，可以看出，centos vg已经扩容。
[root@master ~]# vgdisplay 
  --- Volume group ---
  VG Name               centos
  System ID             
  Format                lvm2
  Metadata Areas        2
  Metadata Sequence No  4
  VG Access             read/write
  VG Status             resizable
  MAX LV                0
  Cur LV                2
  Open LV               2
  Max PV                0
  Cur PV                2
  Act PV                2
  VG Size               28.99 GiB
  PE Size               4.00 MiB
  Total PE              7422
  Alloc PE / Size       2303 / <9.00 GiB
  Free  PE / Size       5119 / <20.00 GiB
  VG UUID               v2nMSm-xUhk-W82l-amgX-u36i-wsjN-IHxuit
  • 对lv进行扩容
[root@master ~]# lvextend -l +100%free /dev/mapper/centos-root
  Size of logical volume centos/root changed from <8.00 GiB (2047 extents) to 27.99 GiB (7166 extents).
  Logical volume centos/root successfully resized.
  • 调整分区大小
[root@master ~]# xfs_growfs /dev/mapper/centos-root
meta-data=/dev/mapper/centos-root isize=512    agcount=4, agsize=524032 blks
         =                       sectsz=512   attr=2, projid32bit=1
         =                       crc=1        finobt=0 spinodes=0
data     =                       bsize=4096   blocks=2096128, imaxpct=25
         =                       sunit=0      swidth=0 blks
naming   =version 2              bsize=4096   ascii-ci=0 ftype=1
log      =internal               bsize=4096   blocks=2560, version=2
         =                       sectsz=512   sunit=0 blks, lazy-count=1
realtime =none                   extsz=4096   blocks=0, rtextents=0
data blocks changed from 2096128 to 7337984
  • 扩容成功
[root@master ~]# df -h
Filesystem               Size  Used Avail Use% Mounted on
/dev/mapper/centos-root   28G  8.0G   20G  29% /

☀2、解压缩包

 tar -xvf mysql-8.0.28-linux-glibc2.12-x86_64.tar

  • 继续解压
tar -xvf mysql-8.0.28-linux-glibc2.12-x86_64.tar.xz

  • 修改目录为mysql
mv mysql-8.0.28-linux-glibc2.12-x86_64 mysql
  • 创建数据文件目录
cd mysql/
mkdir data
mkdir -p /mysql/backup/binarylog/

  • 创建组和用户
groupadd mysql
useradd -g mysql mysql
  • 修改目录权限
chown -R mysql.mysql /home/mysql/
  • 删除系统自带的mariadb
rpm -qa|grep mariadb


[root@master mysql]# rpm -e mariadb-libs-5.5.68-1.el7.x86_64
error: Failed dependencies:
	libmysqlclient.so.18()(64bit) is needed by (installed) postfix-2:2.10.1-9.el7.x86_64
	libmysqlclient.so.18(libmysqlclient_18)(64bit) is needed by (installed) postfix-2:2.10.1-9.el7.x86_64
[root@master mysql]# rpm -e mariadb-libs-5.5.68-1.el7.x86_64 --nodeps
warning: /etc/my.cnf saved as /etc/my.cnf.rpmsave
  • 配置参数文件my.cnf

说明：下面的配置文件中有的参数是过期的。读者在配置时，要自己修改成最新支持的参数。
default_authentication_plugin=mysql_native_password
expire_logs_days=30
character_set_server=utf8
slave_parallel_workers=16

mv /etc/my.cnf /etc/my.cnf.bak
vi /etc/my.cnf
[client]
port=3306
socket=/tmp/mysql.sock

[mysqld]
basedir=/home/mysql
datadir=/home/mysql/data
socket=/tmp/mysql.sock
user=mysql
port=3306
character_set_server=utf8
# symbolic-links=0
# bind-address=0.0.0.0
## 登录验证方式
default_authentication_plugin=mysql_native_password
max_connections=1000

## 配置数据库双主模式
server-id=100
## 开启二进制日志
log-bin=/home/mysql/backup/binarylog/mysql-bin
## 自增长的递增量及偏移量
auto-increment-increment=2
auto-increment-offset=1
## 设置不进行同步的数据库
binlog-ignore-db=mysql
binlog-ignore-db=sys
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
## 并行复制线程数：和cpu核数一致
slave_parallel_workers=16
## binlog日志过期清理时间
expire_logs_days=30
## 开启GTID
gtid-mode=ON
enforce-gtid-consistency=on
secure-file-priv=''
[mysqld_safe]
log-error=/home/mysql/data/error.log
pid-file=/home/mysql/data/mysqld.pid
tmpdir=/tmp
sql_mode=NO_ZERO_DATE,NO_ZERO_IN_DATE

❤ 二、开始安装mysql

☀1、初始化

cd /home/mysql/bin
./mysqld --defaults-file=/etc/my.cnf --basedir=/home/mysql/ --datadir=/home/mysql/data/ --user=mysql --initialize
2023-08-06T11:16:24.489716Z 0 [Warning] [MY-011068] [Server] The syntax 'slave_parallel_workers' is deprecated and will be removed in a future release. Please use replica_parallel_workers instead.
2023-08-06T11:16:24.489731Z 0 [Warning] [MY-011068] [Server] The syntax 'expire-logs-days' is deprecated and will be removed in a future release. Please use binlog_expire_logs_seconds instead.
2023-08-06T11:16:24.489831Z 0 [Warning] [MY-010918] [Server] 'default_authentication_plugin' is deprecated and will be removed in a future release. Please use authentication_policy instead.
2023-08-06T11:16:24.489847Z 0 [System] [MY-013169] [Server] /home/mysql/bin/mysqld (mysqld 8.0.28) initializing of server in progress as process 2918
2023-08-06T11:16:24.492088Z 0 [Warning] [MY-013242] [Server] --character-set-server: 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
2023-08-06T11:16:24.581516Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2023-08-06T11:16:39.472251Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2023-08-06T11:16:58.859653Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: ,xE*w-f5hrd(

☀2、添加启动

cp support-files/mysql.server /etc/init.d/mysql
chkconfig --add mysql
chkconfig --list mysql

☀3、启动

./support-files/mysql.server start

☀4、修改root用户密码

  • 不修改密码，无法进行操作
[root@master bin]# ./mysql -uroot -p 
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.28

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
  • 修改密码
ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';

修改完成后，操作可以正常进行

主库安装成功

  • 插入一下实验数据
  • 创建school库
CREATE DATABASE `school`;
use school

CREATE TABLE `grade`(
	`GradeID` INT(1) NOT NULL COMMENT '年级编号',
	`GradeName` VARCHAR(20) NOT NULL COMMENT '年级名称',
	PRIMARY KEY (`GradeID`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;
-- 年级数据：
INSERT INTO `grade` VALUES
(1,'大一'),(2,'大二'),(3,'大三'),(4,'大四');
  • 创建表student
CREATE TABLE `student` (
  `StudentNo` mediumint NOT NULL COMMENT '学号',
  `LoginPwd` varchar(255) COMMENT '登陆密码',
  `Name` varchar(255) default NULL COMMENT '学生姓名',
  `Gender` varchar(255) default NULL COMMENT '性别',
  `Phone` varchar(100) default NULL COMMENT '联系电话,允许为空，可选输入',
  `GradeId` varchar(255) default NULL COMMENT '年级',
  `Address` varchar(255) COMMENT '地址',
  `Birth` DATETIME COMMENT '出生时间',
  `Email` varchar(255) default NULL COMMENT '邮箱',
  `IDCard` varchar(13) default NULL COMMENT '身份证号',
  PRIMARY KEY (`StudentNo`),
  UNIQUE KEY `IDCard` (`IDCard`),
  KEY `Email` (`Email`)
) AUTO_INCREMENT=1 ENGINE=INNODB DEFAULT CHARSET=utf8;

-- 学生数据如下：共200条

INSERT INTO `student` (`StudentNo`,`LoginPwd`,`Name`,`Gender`,`Phone`,`GradeId`,`Address`,`Birth`,`Email`,`IDCard`) VALUES (10001,"3074","Roanna","female","(323) 505-2056","1","Épinal","2000-04-18 07:32:36","id.magna@auctorvelitAliquam.co.uk","16811101 9181"),(10002,"5067","Wanda","female","(521) 102-9181","2","Kontich","2000-06-10 21:12:39","vestibulum@nequetellusimperdiet.edu","16451023 2202"),(10003,"1200","Lila","female","(830) 232-6495","3","Łomża","2000-02-13 20:55:31","ac.mattis@velmauris.edu","16550309 4426"),(10004,"1518","Damian","female","(727) 438-4260","1","Chandler","2000-08-05 02:32:51","et.eros@atrisus.net","16130219 6652"),(10005,"4033","Zeph","female","(315) 639-6899","2","Stalowa Wola","2000-01-02 12:22:42","est@Nullatempor.net","16150907 1245"),(10006,"8300","Wynne","female","(813) 242-6704","4","Recco","1999-12-26 07:35:36","ut.eros@ipsumnon.com","16410325 6741"),(10007,"8274","Rogan","female","(338) 362-0943","1","Castri di Lecce","1999-06-13 08:35:20","iaculis.aliquet.diam@Aliquam.com","16400513 8922"),(10008,"9199","Maxwell","male","(726) 474-9574","3","Little Rock","1999-07-17 22:28:21","sed.sapien@tinciduntnunc.edu","16301124 3296"),(10009,"4729","Sheila","male","(717) 378-7174","3","Erie","2000-03-14 00:02:58","dui.semper@eteros.org","16810525 4091"),(10010,"1973","Chantale","male","(351) 388-8842","4","Tasikmalaya","2000-04-20 18:50:21","Nullam@molestie.org","16751123 5611");
INSERT INTO `student` (`StudentNo`,`LoginPwd`,`Name`,`Gender`,`Phone`,`GradeId`,`Address`,`Birth`,`Email`,`IDCard`) VALUES (10011,"3800","Logan","female","(855) 154-1347","2","Rance","1999-05-04 04:03:36","aliquet@nunc.ca","16350522 6344"),(10012,"1674","Neil","female","(592) 910-7632","4","Adoni","2000-11-05 13:47:54","non.egestas@tellusPhasellus.ca","16671222 3673"),(10013,"9815","Georgia","female","(773) 212-7683","4","Archennes","2000-09-11 20:44:34","at.iaculis.quis@SednequeSed.org","16881127 5091"),(10014,"9462","Ebony","male","(114) 817-2113","1","Wyoming","2000-02-13 15:12:27","nec.cursus.a@pede.edu","16110701 0728"),(10015,"2778","Preston","male","(891) 962-8039","4","Abaetetuba","1999-05-05 00:09:19","nec@Morbimetus.co.uk","16360729 5072"),(10016,"2369","Jermaine","female","(402) 289-2888","3","Santo Stefano del Sole","1999-08-25 17:59:14","in.faucibus@estmollis.co.uk","16280616 4204"),(10017,"9876","Mallory","male","(808) 912-7250","2","Rathenow","2000-04-22 21:31:46","metus.In.nec@metus.net","16010610 9051"),(10018,"6566","Avye","female","(560) 697-3827","2","Bedollo","1999-08-18 04:11:47","eros.turpis.non@ipsumprimis.edu","16970513 2687"),(10019,"8629","Chase","male","(170) 567-9101","3","Valbrevenna","1999-10-21 09:26:26","per.inceptos.hymenaeos@ullamcorperDuis.ca","16210614 7396"),(10020,"3767","Judith","female","(556) 416-2606","4","Duncan","2000-08-30 16:39:13","nulla.at.sem@malesuadafringillaest.net","16171128 2200");
INSERT INTO `student` (`StudentNo`,`LoginPwd`,`Name`,`Gender`,`Phone`,`GradeId`,`Address`,`Birth`,`Email`,`IDCard`) VALUES (10021,"2170","Ori","male","(332) 111-1672","3","Sars-la-Buissire","2000-01-23 23:02:13","et.magna@Pellentesqueultriciesdignissim.ca","16040911 4691"),(10022,"7645","Noelle","female","(741) 639-3344","4","Vico nel Lazio","2000-04-18 04:19:15","arcu.iaculis.enim@porttitorinterdumSed.co.uk","16650814 9033"),(10023,"1537","Herman","male","(482) 675-8775","3","Wood Buffalo","2001-02-11 23:42:40","amet@convallisdolor.net","16051224 5697"),(10024,"8493","Orli","female","(657) 851-0898","2","Tiegem","2000-06-20 10:25:36","ultrices@Aenean.org","16700216 8800"),(10025,"7168","Brynn","male","(372) 272-2797","3","Conselice","1999-04-18 19:24:29","ullamcorper@Vivamuseuismodurna.org","16370418 2694"),(10026,"8497","Kelly","male","(167) 327-4944","2","Narowal","2000-03-24 01:45:46","ipsum.sodales@ipsumPhasellusvitae.org","16470807 9274"),(10027,"7590","Honorato","male","(353) 202-8536","4","Hualpén","2000-09-30 19:07:05","non.ante.bibendum@Donec.com","16470729 9410"),(10028,"9326","Violet","female","(951) 281-9997","4","Tacoma","2000-12-30 23:42:36","eros.nec@temporarcuVestibulum.net","16280323 9546"),(10029,"1687","Summer","female","(396) 662-1902","2","Großpetersdorf","2000-07-13 19:13:02","turpis.nec.mauris@Sedauctorodio.com","16140716 8630"),(10030,"8335","Zelenia","female","(406) 575-8418","2","Bokaro Steel City","2001-01-09 22:03:42","consequat.enim.diam@nonsapien.net","16701029 2873");
INSERT INTO `student` (`StudentNo`,`LoginPwd`,`Name`,`Gender`,`Phone`,`GradeId`,`Address`,`Birth`,`Email`,`IDCard`) VALUES (10031,"7285","May","male","(206) 508-8925","4","Saint-Denis-Bovesse","1999-05-28 03:19:20","nisi@Nullamvitae.net","16080104 1625"),(10032,"5832","Logan","female","(955) 838-3533","2","Yellowhead County","2001-02-28 09:54:49","nulla.vulputate@dapibusquamquis.net","16780227 6332"),(10033,"2743","MacKensie","female","(615) 702-4257","1","Volokonovka","2001-03-13 09:28:31","faucibus@etmalesuada.net","16350619 0044"),(10034,"2554","Lareina","male","(207) 417-4357","3","Oaxaca","2000-09-15 15:35:36","Aliquam.erat@luctusipsum.org","16050127 5010"),(10035,"4370","Susan","male","(355) 749-1428","1","Kallo","2001-01-02 03:06:27","adipiscing.lobortis@nibhDonecest.ca","16830208 9993"),(10036,"4761","Branden","male","(129) 562-2679","3","Rocca Santo Stefano","2000-07-31 19:38:41","Phasellus@purusMaecenaslibero.org","16810915 6581"),(10037,"6268","Oliver","female","(330) 204-5219","1","Murree","2000-10-12 17:37:18","primis.in.faucibus@etipsumcursus.org","16910907 1762"),(10038,"3096","Gay","female","(313) 870-1744","4","Şereflikoçhisar","2000-09-29 00:44:18","non.vestibulum.nec@elementum.edu","16871004 9746"),(10039,"8834","Ria","male","(789) 424-7591","4","Loy","1999-10-06 01:11:01","erat.vel@diam.org","16131105 5691"),(10040,"6203","Dolan","male","(796) 144-1850","1","Snezhinsk","2001-03-12 11:41:17","ipsum@ligulatortor.co.uk","16110807 8393");
INSERT INTO `student` (`StudentNo`,`LoginPwd`,`Name`,`Gender`,`Phone`,`GradeId`,`Address`,`Birth`,`Email`,`IDCard`) VALUES (10041,"9732","Tallulah","female","(930) 862-0731","3","Tank","2001-01-10 20:03:56","posuere.at.velit@eratvolutpatNulla.co.uk","16080118 3377"),(10042,"4682","Martha","male","(744) 522-4997","1","Evesham","1999-07-22 14:35:35","dolor@tinciduntneque.org","16840720 4216"),(10043,"6442","Caesar","male","(561) 983-2221","4","Chandannagar","2000-09-12 01:05:25","pede@pedeultrices.com","16440108 1064"),(10044,"1808","Inga","male","(210) 882-9858","3","Açailândia","2000-06-11 01:10:15","egestas@Nunccommodoauctor.ca","16450114 6072"),(10045,"3276","Idola","male","(742) 860-3321","3","Warminster","1999-10-05 23:33:21","ut@nibhAliquam.com","16370724 8617"),(10046,"7410","Allistair","female","(470) 218-5854","1","Máfil","2001-03-26 05:55:31","mi.pede@nascetur.org","16910525 0063"),(10047,"6560","Macey","male","(238) 934-7916","4","Ceranesi","1999-08-27 23:13:33","ipsum@risus.com","16071025 1349"),(10048,"5322","Hyatt","female","(973) 383-6943","1","Schwalbach","2000-05-01 11:48:13","vehicula.aliquet@tincidunttempusrisus.net","16641226 7855"),(10049,"7673","Sigourney","female","(617) 472-7649","3","Osimo","2000-07-28 10:03:05","sed@quisaccumsanconvallis.net","16120606 4683"),(10050,"3112","Dara","female","(203) 917-2419","3","Temploux","2000-12-01 21:29:05","aliquam@tortor.com","16610822 0572");
INSERT INTO `student` (`StudentNo`,`LoginPwd`,`Name`,`Gender`,`Phone`,`GradeId`,`Address`,`Birth`,`Email`,`IDCard`) VALUES (10051,"6760","Amos","male","(478) 226-6830","3","Livorno","1999-09-08 05:46:13","mattis@sedconsequatauctor.co.uk","16131217 8690"),(10052,"8390","Melinda","male","(586) 616-5838","1","Caramanico Terme","2001-03-17 03:44:41","posuere.cubilia@sem.com","16750710 0795"),(10053,"3594","Ramona","male","(119) 532-8120","1","Hoofddorp","2001-02-01 08:37:19","auctor@adipiscing.com","16330329 4056"),(10054,"2072","Daryl","female","(204) 524-5540","1","Nakusp","2000-12-12 03:40:47","molestie@Etiamligulatortor.com","16850812 5013"),(10055,"2127","Grant","male","(540) 572-3967","1","Maple Creek","1999-04-27 13:12:05","tortor.Integer.aliquam@gravidamauris.ca","16600214 8556"),(10056,"3953","Howard","female","(149) 575-8574","4","Colombes","2000-03-17 06:41:18","ridiculus.mus@Suspendisse.co.uk","16690817 7998"),(10057,"6821","Ishmael","male","(327) 911-0776","3","Castiglione di Sicilia","2000-10-15 00:37:07","rutrum@eueratsemper.org","16260606 1055"),(10058,"1716","Yen","female","(935) 482-4765","2","Castello Tesino","1999-06-09 23:33:32","egestas.lacinia.Sed@Quisque.org","16070311 0098"),(10059,"3951","Cade","female","(801) 882-3259","4","San Isidro","1999-11-17 12:22:34","semper.dui.lectus@tellus.com","16551009 4724"),(10060,"1220","Hashim","male","(392) 515-7311","4","Wierde","2000-06-13 03:14:40","gravida.sit@lectus.ca","16680119 0122");
INSERT INTO `student` (`StudentNo`,`LoginPwd`,`Name`,`Gender`,`Phone`,`GradeId`,`Address`,`Birth`,`Email`,`IDCard`) VALUES (10061,"3222","Willa","female","(314) 640-8126","2","Sullana","2000-02-29 01:13:47","erat.vitae.risus@doloregestas.edu","16130519 2831"),(10062,"2314","Sonia","female","(873) 515-3725","2","Pettoranello del Molise","1999-06-22 19:43:35","vel.nisl@atpedeCras.co.uk","16980112 0859"),(10063,"9628","Zahir","female","(487) 745-6510","1","Blevio","2000-08-25 08:49:17","vulputate.risus@semegestas.com","16210206 1617"),(10064,"8629","Hope","female","(122) 766-9684","4","Fresia","2000-04-28 15:50:12","nunc.sed.libero@libero.co.uk","16391123 8800"),(10065,"3687","Shoshana","male","(933) 150-6125","3","Hermosillo","2000-10-08 01:24:20","amet@Quisquetinciduntpede.co.uk","16160821 2203"),(10066,"3606","Gareth","male","(328) 269-7400","1","Vaughan","1999-10-30 10:15:59","est@sodalesnisimagna.co.uk","16121123 3208"),(10067,"9433","Piper","male","(214) 925-7984","1","Cisano Bergamasco","2000-01-14 22:54:29","nulla.at@dictum.edu","16620907 5867"),(10068,"6983","Kennedy","male","(659) 372-9206","3","Alnwick","2000-12-04 07:34:05","non.bibendum@nulla.co.uk","16990308 4029"),(10069,"8176","Irma","female","(441) 321-1276","2","Renfrew","1999-05-06 00:02:36","nostra.per.inceptos@imperdiet.net","16160125 1133"),(10070,"6913","Gavin","male","(877) 457-6858","3","Terme","1999-04-30 00:54:13","a.arcu@Duismienim.com","16940802 4009");
INSERT INTO `student` (`StudentNo`,`LoginPwd`,`Name`,`Gender`,`Phone`,`GradeId`,`Address`,`Birth`,`Email`,`IDCard`) VALUES (10071,"9301","Xantha","male","(154) 904-2155","1","Carleton","1999-09-18 04:01:05","orci@nequesedsem.org","16531217 5101"),(10072,"9047","Camden","male","(860) 354-2246","3","Chañaral","2000-01-05 01:59:58","morbi.tristique@duiCras.ca","16291211 9043"),(10073,"9329","Athena","female","(909) 761-7133","1","Vaughan","2001-01-09 15:34:36","Sed.congue@sem.org","16081128 0916"),(10074,"7554","Louis","male","(746) 566-7343","1","Icheon","2000-09-15 20:18:30","Curabitur.consequat.lectus@luctus.edu","16200403 2054"),(10075,"8200","Kenneth","male","(241) 358-7716","4","Llandrindod Wells","2000-02-25 18:25:14","non@maurissapiencursus.org","16620114 5122"),(10076,"9061","Randall","female","(749) 279-8690","4","Sohbatpur","2000-11-20 07:26:15","augue.eu@Donectempus.net","16061203 8422"),(10077,"8543","Jerome","male","(108) 183-8613","4","Orosei","2000-09-08 12:02:36","sit@Namtempordiam.org","16291214 3993"),(10078,"6300","Maya","female","(293) 906-7499","4","Grayvoron","1999-07-15 19:13:07","vel.est@eleifendnuncrisus.co.uk","16050218 0284"),(10079,"3937","Hedda","female","(399) 429-1800","1","Heredia","2000-10-17 03:52:47","Morbi.neque@nonarcuVivamus.co.uk","16570517 1386"),(10080,"3804","Gareth","female","(533) 754-0122","1","Svitino","1999-06-09 14:01:37","taciti.sociosqu@purusac.edu","16510224 1345");
INSERT INTO `student` (`StudentNo`,`LoginPwd`,`Name`,`Gender`,`Phone`,`GradeId`,`Address`,`Birth`,`Email`,`IDCard`) VALUES (10081,"8293","Margaret","male","(717) 797-2927","3","Jaunpur","1999-05-13 08:18:04","inceptos@ullamcorpernislarcu.co.uk","16350301 1813"),(10082,"3633","Marcia","female","(960) 510-2093","4","Bremen","2000-01-04 04:49:22","adipiscing@sem.com","16570703 0747"),(10083,"6890","Tashya","male","(950) 486-4969","4","Norfolk County","1999-08-19 11:44:54","mauris.ut.mi@aliquameu.ca","16930908 7527"),(10084,"7040","Cairo","female","(499) 906-0228","2","West Jordan","2000-04-23 05:11:12","non.arcu@elita.com","16080821 8051"),(10085,"4391","Lucas","male","(652) 110-5570","1","Gualdo Tadino","1999-07-20 21:53:17","amet@lacusUt.ca","16850626 2115"),(10086,"4975","Gray","male","(412) 254-4585","3","Levin","1999-11-14 05:18:58","aptent.taciti.sociosqu@DonecegestasAliquam.ca","16001023 5901"),(10087,"9688","Colin","male","(979) 563-9734","3","Wrexham","2000-06-18 07:19:03","neque@volutpatnuncsit.ca","16510420 3285"),(10088,"8706","Anne","female","(171) 639-3734","2","Munger","2000-07-31 09:10:47","mauris.Morbi.non@dapibusrutrum.org","16180516 4686"),(10089,"1693","Walter","female","(615) 494-5344","3","Gistel","2001-03-19 04:40:13","dui.Cras@euultrices.org","16380215 0353"),(10090,"8366","Gavin","male","(208) 936-2086","2","Campofelice di Fitalia","1999-08-12 19:32:28","ridiculus.mus@auctorvelit.org","16571210 6151");
INSERT INTO `student` (`StudentNo`,`LoginPwd`,`Name`,`Gender`,`Phone`,`GradeId`,`Address`,`Birth`,`Email`,`IDCard`) VALUES (10091,"3700","Ruth","male","(610) 334-0543","4","Noragugume","1999-12-25 16:31:47","libero@ultricessitamet.org","16930219 1987"),(10092,"2803","Lawrence","female","(479) 886-5256","3","Ulyanovsk","1999-09-10 08:38:05","id.magna.et@turpisNulla.org","16430223 0026"),(10093,"2454","Brody","male","(629) 779-6201","1","Fulda","2001-02-23 23:40:11","purus.Nullam.scelerisque@euismodmauris.com","16971115 0160"),(10094,"6116","Tyrone","female","(196) 152-8261","3","San Ramón","2000-03-30 20:25:42","dignissim.pharetra.Nam@parturientmontes.ca","16700229 4754"),(10095,"2405","Martin","female","(795) 323-7952","1","Picture Butte","1999-10-01 16:10:01","posuere@convallis.com","16120320 3334"),(10096,"8049","Hadassah","female","(194) 835-0732","4","San Luis Río Colorado","2000-08-19 16:55:46","gravida.sit@dolorNulla.co.uk","16880411 1782"),(10097,"6488","Kaye","female","(801) 135-5286","2","Córdoba","2001-03-19 03:13:01","cursus.et@sem.net","16970908 9446"),(10098,"6873","Mara","male","(921) 174-6896","2","Palangka Raya","1999-08-15 03:34:17","a.ultricies.adipiscing@arcuimperdiet.org","16170714 3887"),(10099,"6953","Bell","male","(279) 484-8297","2","Helena","2000-03-27 19:16:34","dolor@leo.edu","16040207 5519"),(10100,"2114","Jacob","male","(683) 602-0487","4","Norrköping","1999-10-08 19:15:15","dolor.dapibus@tempuseu.edu","16831017 1650");

INSERT INTO `student` (`StudentNo`,`LoginPwd`,`Name`,`Gender`,`Phone`,`GradeId`,`Address`,`Birth`,`Email`,`IDCard`) VALUES (10101,"6883","Nigel","male","(461) 780-3702","4","Calera de Tango","2000-10-07 15:35:32","aliquet@Nam.co.uk","16940130 6833"),(10102,"5392","Olympia","male","(900) 815-7381","3","Burnaby","2000-06-11 18:14:46","sit@Morbimetus.ca","16550118 0748"),(10103,"2833","Declan","male","(391) 358-1661","3","Vanier","2000-10-11 21:39:53","mauris.id@ipsumdolorsit.co.uk","16700728 1079"),(10104,"2489","Kaitlin","male","(573) 479-5894","1","Kidderminster","1999-05-26 11:45:53","urna@Infaucibus.net","16020625 7438"),(10105,"2964","Jesse","male","(164) 328-0166","4","Fort Worth","2000-05-01 12:33:53","tellus@velfaucibus.edu","16910527 2984"),(10106,"7855","Callum","male","(824) 484-5043","2","Montgomery","1999-04-26 22:28:17","non@molestiedapibus.co.uk","16910422 8714"),(10107,"3030","Ingrid","male","(178) 169-8661","3","Rotheux-RimiŽre","1999-07-26 04:53:30","Sed.diam.lorem@ligula.co.uk","16750120 5236"),(10108,"9781","Zachery","female","(179) 907-4611","2","Macchia Valfortore","1999-08-13 05:45:36","cursus.Integer.mollis@scelerisquemollis.org","16370629 0727"),(10109,"6192","Ivor","female","(250) 795-6806","2","Vijayawada","2000-08-15 17:36:48","consequat@quis.co.uk","16420409 5634"),(10110,"8705","Gary","male","(182) 946-0569","2","San Isidro","2000-09-29 06:48:17","feugiat.placerat@risusQuisque.edu","16690602 7955");
INSERT INTO `student` (`StudentNo`,`LoginPwd`,`Name`,`Gender`,`Phone`,`GradeId`,`Address`,`Birth`,`Email`,`IDCard`) VALUES (10111,"1179","Burke","male","(442) 952-6427","2","Bosa","1999-05-19 08:31:51","Fusce.dolor@ipsumdolor.org","16220126 2918"),(10112,"6866","Lester","female","(426) 763-6378","2","Maglie","2000-09-08 16:57:52","rhoncus.Donec@necleo.co.uk","16111124 0246"),(10113,"8154","Deirdre","male","(797) 413-5651","4","Châlons-en-Champagne","2000-09-21 04:44:37","non@odioNaminterdum.com","16451013 1693"),(10114,"3628","Shoshana","male","(307) 902-7292","3","Fosses-la-Ville","2000-12-18 21:27:15","Phasellus.at.augue@euenimEtiam.org","16340606 0552"),(10115,"5863","Kim","male","(195) 469-6492","2","WagnelŽe","2000-09-27 13:37:03","vulputate.mauris.sagittis@primisinfaucibus.ca","16050712 8262"),(10116,"8613","Mallory","male","(422) 759-4260","4","Kufstein","1999-07-29 14:22:32","Nunc.sed.orci@convallis.org","16150306 9914"),(10117,"9415","Theodore","male","(183) 554-0520","2","Kerkrade","2000-05-01 13:51:42","nibh.Aliquam@idsapien.net","16040519 9837"),(10118,"4225","Halla","female","(349) 611-8705","1","Buguma","1999-05-05 22:11:16","arcu.Curabitur@rutrummagna.net","16410923 7547"),(10119,"8149","Grady","female","(347) 442-8164","2","Kamalia","2000-12-09 15:06:13","ultrices@egetnisidictum.com","16570322 4161"),(10120,"5061","Davis","female","(556) 968-5309","1","Fort Wayne","1999-07-13 03:05:41","mattis@ipsumDonecsollicitudin.net","16990929 4903");
INSERT INTO `student` (`StudentNo`,`LoginPwd`,`Name`,`Gender`,`Phone`,`GradeId`,`Address`,`Birth`,`Email`,`IDCard`) VALUES (10121,"8478","Jemima","male","(604) 450-3040","4","Oldenzaal","2000-12-02 07:17:53","eu@tinciduntnunc.ca","16001127 8090"),(10122,"8836","Blake","female","(113) 931-5272","4","Hallein","2000-07-17 14:45:24","Quisque@non.com","16220926 4163"),(10123,"3178","Hadassah","male","(996) 748-2518","2","Saint-L�onard","1999-12-08 20:06:13","orci.Donec.nibh@eratnonummyultricies.co.uk","16620224 6846"),(10124,"3146","Karleigh","female","(287) 601-3115","2","Laarne","1999-05-19 23:15:08","eu.lacus@quam.com","16921003 3172"),(10125,"9819","Uma","female","(841) 615-7397","4","Kalisz","2000-12-28 04:03:43","velit.Aliquam.nisl@necdiamDuis.co.uk","16171125 9604"),(10126,"6287","Katelyn","female","(622) 431-8036","1","Seevetal","2000-05-05 11:43:04","id@ametorciUt.com","16020916 3559"),(10127,"3926","Hoyt","male","(681) 471-7497","2","Körfez","2000-09-14 08:20:06","ipsum@iaculis.org","16770711 6252"),(10128,"7487","Demetrius","female","(116) 106-7116","3","Sint-Denijs-Westrem","1999-10-09 12:46:14","nisi@nisi.co.uk","16930618 6694"),(10129,"6154","Steven","male","(254) 487-0714","3","Crieff","2000-03-17 07:33:26","sagittis.augue.eu@tristique.ca","16861207 1533"),(10130,"2726","Natalie","female","(992) 959-9294","2","Salem","2001-01-17 14:26:00","mi.enim@adipiscinglobortis.co.uk","16170725 9170");
INSERT INTO `student` (`StudentNo`,`LoginPwd`,`Name`,`Gender`,`Phone`,`GradeId`,`Address`,`Birth`,`Email`,`IDCard`) VALUES (10131,"3701","Maxwell","female","(427) 237-8847","3","Kitchener","2000-08-30 13:59:56","ipsum.porta.elit@quamvelsapien.com","16730930 6673"),(10132,"4138","Tanek","female","(168) 332-2102","1","Freirina","2000-10-17 18:16:13","leo.Cras@pharetra.ca","16800221 6334"),(10133,"1321","Germane","female","(214) 370-2958","1","Pushchino","1999-07-30 08:05:27","accumsan.convallis.ante@enimCurabiturmassa.co.uk","16740404 9608"),(10134,"3180","Keaton","female","(798) 464-5672","3","Ponoka","2001-01-30 10:26:31","quis@adipiscing.co.uk","16090120 8090"),(10135,"8257","Nina","female","(891) 184-5310","2","Saint-L�onard","1999-09-10 02:29:30","In.tincidunt.congue@nunc.co.uk","16080813 6758"),(10136,"6561","Elijah","female","(296) 499-1992","3","Chile Chico","2000-07-03 13:41:53","ut.aliquam.iaculis@eratEtiamvestibulum.ca","16971225 9614"),(10137,"8751","Clare","female","(545) 130-0984","4","Oelegem","1999-09-11 16:57:36","Donec.feugiat@ante.org","16040917 1949"),(10138,"7688","Raven","female","(288) 730-0509","2","Rotheux-RimiŽre","2000-04-15 00:05:30","Proin.velit@Sed.ca","16360223 8929"),(10139,"9128","Rhona","female","(516) 562-1100","4","Valbrevenna","1999-05-02 02:34:52","non@placerat.co.uk","16740104 4016"),(10140,"4242","Rama","female","(645) 712-9788","1","Milena","2000-06-04 21:53:47","ligula.consectetuer.rhoncus@habitantmorbitristique.org","16421027 9107");
INSERT INTO `student` (`StudentNo`,`LoginPwd`,`Name`,`Gender`,`Phone`,`GradeId`,`Address`,`Birth`,`Email`,`IDCard`) VALUES (10141,"1247","Timon","male","(600) 527-1728","4","Affligem","1999-07-24 07:45:15","mi.lorem.vehicula@sagittissemper.com","16000420 9169"),(10142,"5751","Aretha","female","(370) 274-0276","3","Murree","2000-09-05 05:51:22","nisl.Quisque@ametconsectetueradipiscing.org","16970426 9555"),(10143,"3111","Serina","female","(432) 266-0904","2","Laarne","1999-08-18 03:40:13","eu.ultrices@scelerisque.edu","16261208 6005"),(10144,"4597","Grady","female","(375) 495-4980","4","Millesimo","2000-09-13 08:23:58","Donec.sollicitudin@dignissimtemporarcu.edu","16660211 5641"),(10145,"9265","Camille","female","(479) 431-5126","1","Eyemouth","2000-03-22 05:28:33","sagittis@milaciniamattis.ca","16030614 4114"),(10146,"1117","Caleb","female","(126) 107-2135","3","Devon","1999-09-05 10:50:25","a@pedenec.edu","16700104 1065"),(10147,"8842","Felix","female","(193) 889-1551","2","Lambersart","1999-04-19 09:17:26","tincidunt.adipiscing@Praesent.edu","16860823 0275"),(10148,"3224","MacKensie","male","(106) 217-9580","3","Livorno","2001-03-19 19:13:45","Morbi.metus.Vivamus@eulacus.ca","16740105 3355"),(10149,"9684","Samantha","male","(847) 234-5403","1","Pabianice","1999-08-21 12:26:32","eget@semper.net","16630221 9263"),(10150,"6975","Josiah","female","(885) 447-1212","1","Saint-Nicolas","2000-12-26 15:01:34","scelerisque.scelerisque@orcilobortisaugue.com","16761028 3934");
INSERT INTO `student` (`StudentNo`,`LoginPwd`,`Name`,`Gender`,`Phone`,`GradeId`,`Address`,`Birth`,`Email`,`IDCard`) VALUES (10151,"1832","Geoffrey","female","(439) 743-4864","3","Liernu","2000-02-14 22:39:26","faucibus@ut.net","16990601 3413"),(10152,"8609","Connor","male","(334) 654-6333","1","Anjou","2001-03-02 13:02:31","ac.metus@sedfacilisis.ca","16140507 0739"),(10153,"9486","Harriet","female","(631) 573-3038","4","Osan","2000-01-28 12:11:51","sem.Nulla.interdum@interdumfeugiat.com","16850716 0144"),(10154,"5195","Nash","male","(236) 758-1646","3","Hafizabad","1999-08-04 00:57:39","odio.Phasellus.at@pede.com","16130227 8435"),(10155,"9839","Kadeem","male","(687) 497-7652","3","Bergama","2000-03-19 16:29:28","Nulla.eu@ullamcorperviverra.org","16240907 8082"),(10156,"3952","Astra","male","(505) 316-9438","4","Ponte nelle Alpi","2000-08-04 15:45:04","ac@nequeet.ca","16871019 1928"),(10157,"2930","Blaze","male","(925) 467-5670","4","Ciudad Real","1999-12-04 10:08:49","eu@condimentum.org","16201001 5382"),(10158,"5980","Stuart","male","(854) 748-6356","1","Miranda","2000-03-10 02:12:06","volutpat.Nulla.dignissim@nec.ca","16690604 9900"),(10159,"5381","Hilda","male","(323) 353-3580","4","Hohenems","1999-11-01 14:14:12","elit.Curabitur.sed@PhasellusornareFusce.net","16630602 6870"),(10160,"6140","Priscilla","female","(362) 846-7280","4","Rves","2000-12-05 19:10:19","ligula@velitinaliquet.net","16910221 7099");
INSERT INTO `student` (`StudentNo`,`LoginPwd`,`Name`,`Gender`,`Phone`,`GradeId`,`Address`,`Birth`,`Email`,`IDCard`) VALUES (10161,"4712","Yoko","male","(647) 386-5023","2","Fort Wayne","2000-06-28 10:31:33","mauris.a.nunc@et.com","16541209 6827"),(10162,"5828","Cedric","male","(613) 419-2445","4","Bhagalpur","2000-03-10 22:54:49","at.lacus@inmolestietortor.org","16790606 5367"),(10163,"9991","Chase","female","(485) 574-9810","2","Henis","1999-07-06 13:42:03","nisl@turpis.com","16350423 3457"),(10164,"5420","Perry","male","(921) 560-4279","4","Redwater","2000-12-15 21:18:20","eget@velquam.com","16000724 8396"),(10165,"9794","Thor","male","(451) 187-3307","4","Sachs Harbour","2000-10-03 14:34:23","ridiculus.mus.Proin@purus.com","16230609 4323"),(10166,"5426","Denton","male","(427) 843-5803","2","Grand-Reng","2000-10-29 09:20:30","habitant.morbi.tristique@variuseteuismod.org","16690926 1080"),(10167,"8696","Devin","male","(487) 835-9247","1","Herne","1999-09-18 04:18:48","montes.nascetur@necante.co.uk","16640717 1153"),(10168,"9496","Carter","male","(808) 439-2136","2","Metro","2000-07-21 19:33:21","Ut@dolortempus.ca","16640203 3531"),(10169,"3455","Bruce","male","(513) 181-4093","1","San Massimo","1999-04-26 02:21:40","pede.ultrices@rutrum.co.uk","16410920 9033"),(10170,"3205","Naida","female","(383) 800-5033","3","Kohat","2000-12-26 02:59:15","Morbi@gravidanunc.ca","16730727 7355");
INSERT INTO `student` (`StudentNo`,`LoginPwd`,`Name`,`Gender`,`Phone`,`GradeId`,`Address`,`Birth`,`Email`,`IDCard`) VALUES (10171,"4957","Mollie","female","(125) 442-2279","2","Jalandhar (Jullundur)","2000-01-28 14:03:05","purus.accumsan@DonecestNunc.com","16590522 4316"),(10172,"8783","Cain","female","(962) 871-6184","3","Stralsund","1999-07-06 05:33:32","cursus.luctus.ipsum@liberoatauctor.ca","16090420 8113"),(10173,"3873","Belle","female","(883) 631-3315","1","Herne","2000-06-07 12:06:39","molestie.pharetra.nibh@metus.com","16160126 6255"),(10174,"9642","Dakota","male","(435) 144-4385","1","Bersillies-l'Abbaye","2000-08-04 23:30:31","varius.et@Namnullamagna.net","16181213 7394"),(10175,"5604","Isabelle","female","(798) 295-2525","2","Ñuñoa","2001-03-28 14:53:09","urna.convallis@temporarcu.org","16950712 5681"),(10176,"3324","Imelda","male","(922) 752-6824","3","Thame","1999-09-23 09:17:07","dapibus.gravida@pede.org","16111023 1733"),(10177,"2258","Colette","female","(379) 546-9103","3","Kooigem","2000-06-22 22:26:45","Vestibulum.accumsan.neque@rutrumurna.org","16800117 7867"),(10178,"8591","Cedric","male","(185) 577-8787","1","Valverde","2000-02-10 18:12:17","tincidunt.congue.turpis@Nuncpulvinararcu.net","16150419 2608"),(10179,"3349","Travis","female","(662) 629-2194","3","Murree","2000-11-27 15:07:37","ultricies.dignissim@nullamagna.com","16000628 1398"),(10180,"5244","Neville","male","(280) 761-3681","1","Caplan","1999-10-28 08:47:39","eget.ipsum.Suspendisse@orciin.ca","16560508 9290");
INSERT INTO `student` (`StudentNo`,`LoginPwd`,`Name`,`Gender`,`Phone`,`GradeId`,`Address`,`Birth`,`Email`,`IDCard`) VALUES (10181,"8802","Callie","male","(379) 360-3543","4","Mandi Bahauddin","2000-04-04 02:03:23","Phasellus.ornare.Fusce@vel.co.uk","16171119 6137"),(10182,"9953","Oliver","male","(296) 182-0235","1","Sankt Ingbert","1999-10-16 08:25:19","auctor.quis@molestie.net","16760621 6377"),(10183,"5732","Hyatt","female","(209) 535-3579","2","Cap-de-la-Madeleine","2000-10-17 18:17:05","in.consequat.enim@estmollisnon.co.uk","16090125 1025"),(10184,"6533","Megan","male","(899) 221-5186","4","Queanbeyan","1999-11-27 02:50:44","dignissim@eros.net","16181215 3474"),(10185,"2276","Ciaran","male","(406) 644-4582","2","Canmore","2001-02-12 05:28:03","auctor.vitae@sagittisfelisDonec.ca","16670414 5637"),(10186,"5350","Candice","male","(523) 156-3903","1","Jhansi","2001-02-09 09:22:13","massa.Quisque.porttitor@cursusvestibulumMauris.net","16570527 6086"),(10187,"4773","Devin","male","(320) 715-7774","3","Gwangju","2000-04-12 06:31:58","Aliquam.auctor@vitaeeratVivamus.edu","16081106 8964"),(10188,"4350","Ria","female","(807) 335-8864","4","Ulhasnagar","2000-02-26 20:07:15","libero.Donec@maurissagittis.co.uk","16511130 5669"),(10189,"9364","Tobias","female","(941) 326-8820","1","La Thuile","1999-09-21 18:37:41","sem@musProin.ca","16820127 9281"),(10190,"2934","Porter","male","(935) 949-3505","3","Meix-Devant-Virton","2000-10-15 16:13:07","dictum.placerat@velitAliquam.edu","16161227 6962");
INSERT INTO `student` (`StudentNo`,`LoginPwd`,`Name`,`Gender`,`Phone`,`GradeId`,`Address`,`Birth`,`Email`,`IDCard`) VALUES (10191,"8993","Omar","female","(429) 329-5624","1","Wibrin","1999-12-17 02:18:05","sapien.imperdiet.ornare@odiotristique.edu","16840726 1141"),(10192,"8332","Dexter","female","(637) 852-6275","1","Habay","2000-09-09 11:22:44","et.ultrices.posuere@sociis.co.uk","16700204 1429"),(10193,"9855","Violet","female","(931) 460-1253","1","Castelbaldo","1999-09-16 15:14:37","scelerisque@utdolordapibus.ca","16910507 6039"),(10194,"3713","Emerson","male","(102) 866-2086","2","Onoz","2000-12-11 02:47:46","natoque.penatibus@penatibus.ca","16501113 7048"),(10195,"3105","Igor","male","(258) 849-7269","3","Ponoka","2001-01-07 23:27:48","malesuada.id@blanditat.edu","16610802 5906"),(10196,"9145","Sonya","male","(956) 242-9243","4","Cartagena","2000-09-05 13:17:34","rhoncus.Donec.est@ProinvelitSed.org","16930107 4937"),(10197,"5956","Alea","male","(966) 833-3518","1","Padang Sidempuan","1999-07-08 19:22:52","at.pretium.aliquet@tacitisociosqu.net","16730713 7609"),(10198,"1244","Darius","female","(764) 115-2264","3","Asnières-sur-Seine","2000-09-13 19:19:24","tincidunt@sodalesatvelit.org","16490901 9459"),(10199,"5649","Lacota","male","(587) 191-2371","1","Neusiedl am See","1999-08-26 03:32:47","vel.pede@Curabitur.ca","16240904 3367"),(10200,"6812","Austin","female","(864) 672-4438","4","Macchia Valfortore","2000-04-27 09:26:02","nisi.a@Suspendisse.co.uk","16140420 5716");
  • 创建表
/*创建科目表*/
DROP TABLE `subject`;

CREATE TABLE `subject` (
  `SubjectNo` mediumint NOT NULL,
  `SubjectName` varchar(255) default NULL,
  `ClassHour` varchar(255) default NULL,
  PRIMARY KEY (`SubjectNo`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

-- 科目数据：共50条
INSERT INTO `subject` (`SubjectNo`,`SubjectName`,`ClassHour`) VALUES (1001,"Research and Development","37"),(1009,"Finances","41"),(1017,"Accounting","11"),(1025,"Customer Relations","41"),(1033,"Public Relations","19"),(1041,"Customer Service","31"),(1049,"Accounting","29"),(1057,"Customer Relations","47"),(1065,"Customer Service","23"),(1073,"Public Relations","17");
INSERT INTO `subject` (`SubjectNo`,`SubjectName`,`ClassHour`) VALUES (1081,"Research and Development","13"),(1089,"Quality Assurance","37"),(1097,"Legal Department","17"),(1105,"Legal Department","29"),(1113,"Asset Management","11"),(1121,"Research and Development","59"),(1129,"Accounting","31"),(1137,"Human Resources","67"),(1145,"Customer Service","31"),(1153,"Finances","43");
INSERT INTO `subject` (`SubjectNo`,`SubjectName`,`ClassHour`) VALUES (1161,"Sales and Marketing","53"),(1169,"Public Relations","19"),(1177,"Asset Management","23"),(1185,"Legal Department","31"),(1193,"Public Relations","43"),(1201,"Asset Management","29"),(1209,"Human Resources","29"),(1217,"Finances","67"),(1225,"Research and Development","59"),(1233,"Human Resources","53");
INSERT INTO `subject` (`SubjectNo`,`SubjectName`,`ClassHour`) VALUES (1241,"Media Relations","41"),(1249,"Sales and Marketing","11"),(1257,"Legal Department","41"),(1265,"Advertising","23"),(1273,"Payroll","67"),(1281,"Research and Development","61"),(1289,"Customer Relations","61"),(1297,"Customer Relations","13"),(1305,"Customer Relations","17"),(1313,"Accounting","13");
INSERT INTO `subject` (`SubjectNo`,`SubjectName`,`ClassHour`) VALUES (1321,"Media Relations","19"),(1329,"Human Resources","47"),(1337,"Advertising","53"),(1345,"Payroll","11"),(1353,"Legal Department","29"),(1361,"Public Relations","17"),(1369,"Advertising","23"),(1377,"Accounting","59"),(1385,"Payroll","23"),(1393,"Customer Relations","23");
  • 创建表
CREATE TABLE `result` (
  `StudentNo` mediumint default NULL COMMENT '学生学号',
  `SubjectNo` mediumint default NULL COMMENT '科目学号',
  `ExamDate` varchar(255) COMMENT '考试日期',
  `StudentResult` mediumint default NULL COMMENT'考试结果',
  KEY `SubjectNo` (`SubjectNo`),
  KEY `StudentNo` (`StudentNo`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO `result` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentResult`) VALUES (10195,1001,"2020-11-15 23:13:47",78),(10199,1009,"2020-12-30 14:23:38",59),(10183,1017,"2019-07-07 11:29:23",60),(10048,1025,"2020-09-21 14:46:17",56),(10022,1033,"2020-05-25 05:19:54",48),(10101,1041,"2019-04-26 05:31:46",43),(10048,1049,"2019-10-24 05:20:45",51),(10120,1057,"2020-04-29 10:48:27",95),(10082,1065,"2020-04-02 23:06:25",46),(10194,1073,"2020-12-18 12:27:09",64);
INSERT INTO `result` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentResult`) VALUES (10068,1081,"2020-07-07 03:18:56",71),(10105,1089,"2020-10-31 16:01:46",55),(10003,1097,"2019-10-30 21:18:19",40),(10113,1105,"2019-08-17 19:39:18",75),(10059,1113,"2019-07-11 20:06:51",88),(10166,1121,"2020-10-11 18:51:54",72),(10165,1129,"2020-07-03 05:53:26",81),(10151,1137,"2019-10-13 04:18:52",97),(10169,1145,"2020-01-20 17:48:30",76),(10019,1153,"2020-02-05 12:31:08",45);
INSERT INTO `result` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentResult`) VALUES (10095,1161,"2019-09-17 04:12:20",92),(10088,1169,"2019-10-26 20:46:53",53),(10133,1177,"2021-02-02 16:14:46",48),(10156,1185,"2020-04-26 00:16:59",78),(10037,1193,"2020-11-27 17:03:31",81),(10038,1201,"2021-01-19 23:23:07",42),(10181,1209,"2021-03-20 04:50:49",82),(10032,1217,"2020-06-08 13:30:29",92),(10107,1225,"2020-02-27 17:18:31",92),(10015,1233,"2019-11-18 18:28:48",62);
INSERT INTO `result` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentResult`) VALUES (10051,1241,"2019-06-27 16:50:18",74),(10189,1249,"2019-08-28 08:15:00",56),(10005,1257,"2020-05-28 10:41:59",84),(10073,1265,"2020-07-28 12:51:52",56),(10195,1273,"2019-07-02 03:46:34",54),(10193,1281,"2020-11-12 15:11:40",48),(10120,1289,"2020-03-07 16:50:23",58),(10134,1297,"2019-12-27 00:43:10",83),(10140,1305,"2020-10-03 15:34:32",53),(10150,1313,"2019-10-21 17:36:46",57);
INSERT INTO `result` (`StudentNo`,`SubjectNo`,`ExamDate`,`StudentResult`) VALUES (10126,1321,"2020-06-06 02:26:16",89),(10027,1329,"2021-01-03 19:57:37",70),(10117,1337,"2020-07-06 19:51:15",96),(10001,1345,"2019-05-05 00:42:37",78),(10049,1353,"2021-04-01 16:53:35",51),(10085,1361,"2021-01-12 09:15:42",68),(10189,1369,"2021-02-26 20:31:07",67),(10027,1377,"2020-06-30 12:56:44",49),(10084,1385,"2020-04-22 17:24:12",66),(10037,1393,"2019-11-18 17:10:48",85);
  • 如果root的临时密码找不到怎么办？
./support-files/mysql.server stop
./mysql.server start --skip-grant-tables
mysql -uroot -p
use mysql
update user set authentication_string='' where user='root';
exit
./support-files/mysql.server start
mysql -uroot -p
ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';

按照以上步骤在192.168.159.57这台服务器上安装同样的mysql

❤ 三、开始配置主从

☀1、创建同步用户

  • 在主库创建用户rep
CREATE USER 'rep'@'192.168.159.57' IDENTIFIED WITH mysql_native_password BY '123456';
GRANT REPLICATION SLAVE ON *.* TO 'rep'@'192.168.159.57';
flush privileges;

☀2、获取主节点当前binary log文件名和位置（position）

 SHOW MASTER STATUS;

☀3、在从（Slave）节点上设置主节点参数

  • 备库进行操作
CHANGE MASTER TO
MASTER_HOST='192.168.159.56',
MASTER_USER='rep',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000002',
MASTER_PORT=3306,
MASTER_LOG_POS=20593;

☀4、同步数据

需要在备库提前创建好数据库。

  • 备库创建school数据库
create database school;

4.1 第一种方法

  • 主库导出
./mysqldump -uroot -p school  > /home/school_on.sql
  • 备库导入
[root@master01 bin]# ./mysql -uroot -p school < /home/school_on.sql
Enter password:
ERROR 3546 (HY000) at line 24: @@GLOBAL.GTID_PURGED cannot be changed: the added gtid set must not overlap with @@GLOBAL.GTID_EXECUTED

有报错:ERROR 3546

解放方案:
show variables like ‘gtid%’;
如何解决，分情况而言。
1.如果是搭建从库的话：
可以先执行一句reset master在进行set就不会报错。
2.如果是修复数据的话：
一定要确定好自己的集合完全包含当前机器的gtid_purged集合的时候再reset master。

4.2 第二中方法

  • 主库导出数据库
./mysqldump -uroot -p school --set-gtid-purged=OFF > /home/school.sql
  • 备库导入数据
  • 备库需要创建school库
./mysql -uroot -p school < /home/school.sql
  • 主库中查询数据
select count(*) from school.result;
select count(*) from school.subject;
select count(*) from school.student;
select count(*) from school.grade;

  • 备库中查询数据
select count(*) from school.result;
select count(*) from school.subject;
select count(*) from school.student;
select count(*) from school.grade;

☀5、启动slave

mysql> start slave;
Query OK, 0 rows affected, 1 warning (1.11 sec)

☀6、查询主从状态

show slave status\G

2个yes说明同步成功。

☀7、验证同步

7.1、主库插入数据

INSERT INTO school.grade VALUES (5,'大一'),(6,'大二'),(7,'大三'),(8,'大四');
  • 查询数据
select * from school.grade;

7.2、备库查询数据

mysql> select * from school.grade;


说明数据可以进行同步。

❤ 四、升级数据库

☀1、升级检查

./mysqlcheck -u root -p --all-databases --check-upgrade
  • 分区表检查
SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE ENGINE NOT IN ('innodb', 'ndbcluster')
AND CREATE_OPTIONS LIKE '%partitioned%';

SELECT DISTINCT NAME, SPACE, SPACE_TYPE FROM INFORMATION_SCHEMA.INNODB_TABLES
  WHERE NAME LIKE '%#P#%' AND SPACE_TYPE NOT LIKE 'Single';

☀2、全库备份

mkdir -p /backup
mysqldump -u root -p --all-databases > /backup/all_databases.sql

☀3、解压新版本

tar -xvf mysql-8.0.34-linux-glibc2.12-x86_64.tar
tar -xvf mysql-8.0.34-linux-glibc2.12-x86_64.tar.xz
  • 修改名称
mv /home/mysql-8.0.34-linux-glibc2.12-x86_64 /home/mysql8034

☀4、关闭备库

stop slave;
exit
service mysql stop

☀5、开始执行升级

cd /home/mysql8034/bin
./mysqld_safe --user=mysql --datadir=/home/mysql/data &

  • 查看升级情况
[root@slave bin]# jobs
[1]+  Running                 ./mysqld_safe --user=mysql --datadir=/home/mysql/data &
  • 查看日志
tail -f /home/mysql/data/error.log

☀6、重启数据库

mysqladmin -uroot -p shutdown
  • 启动
[root@master01 bin]# service mysql start
Starting MySQL.........The server quit without updating PID[FAILED]home/mysql/data/master01.pid).

有报错，查看错误日志

[root@master01 data]# tail -200 error.log 
2023-08-07T01:57:26.482288Z 1 [ERROR] [MY-012526] [InnoDB] Upgrade is not supported after a crash or shutdown with innodb_fast_shutdown = 2. This
 redo log was created with MySQL 8.0.28, and it appears logically non empty. Please follow the instructions at http://dev.mysql.com/doc/refman/8.
0/en/upgrading.html

错误原因：和/home/mysql/data目录下的ib_logfile0、ib_logfile1有关。

把这2个文件备走就行。

mkdir 8028logbak
mv ib_logfile* 8028logbak

然后再启动

service mysql start

后面把各个环境变量修改成/home/mysql8035。

  • 在主库上面做同样的操作、升级完成。

❤ 总结

以上安装方式，大家不一定要使用，下面是官方提供安装步骤

$> groupadd mysql
$> useradd -r -g mysql -s /bin/false mysql
$> cd /usr/local
$> tar xvf /path/to/mysql-VERSION-OS.tar.xz
$> ln -s full-path-to-mysql-VERSION-OS mysql
$> cd mysql
$> mkdir mysql-files
$> chown mysql:mysql mysql-files
$> chmod 750 mysql-files
$> bin/mysqld --initialize --user=mysql
$> bin/mysql_ssl_rsa_setup
$> bin/mysqld_safe --user=mysql &
# Next command is optional
$> cp support-files/mysql.server /etc/init.d/mysql.server

免责声明：

1、本站资源由自动抓取工具收集整理于网络。

2、本站不承担由于内容的合法性及真实性所引起的一切争议和法律责任。

3、电子书、小说等仅供网友预览使用，书籍版权归作者或出版社所有。

4、如作者、出版社认为资源涉及侵权，请联系本站，本站将在收到通知书后尽快删除您认为侵权的作品。

5、如果您喜欢本资源，请您支持作者，购买正版内容。

6、资源失效，请下方留言，欢迎分享资源链接

文章评论

共0条评论