使用通用二进制方式安装安装mariadb-5.5.43-linux-x86_64.tar.gz
卸载之前的mysql
| |
| service mysqld stop |
| |
| rpm -e mysql-server |
| |
| rm /var/1og/nysqld.log.rpnsave |
| |
创建mysql用户和组
运行mysql进程的都是mysql用户
| |
| groupadd -r mysql |
| |
| |
| |
| |
| useradd -r mysql -g mysql |
| |
| |
| |
| |
| |
| [root@localhost ~] |
| uid=498(mysql) gid=498(mysql) 组=498(mysql) |
| |
展开mysql通用二进制包
| |
| |
| tar xf mariadb-5.5.43-linux-x86_64.tar.gz -C /usr/local |
| |
| |
| |
| |
| |
创建符号链接
| |
| [root@localhost ~] |
| |
| |
| [root@localhost local] |
| "mysql" -> "mariadb-5.5.43-linux-x86_64" |
| |
| |
| |
| |
| |
| |
| [root@localhost local] |
| 总用量 44 |
| drwxr-xr-x. 2 root root 4096 9月 23 2011 bin |
| drwxr-xr-x. 2 root root 4096 9月 23 2011 etc |
| drwxr-xr-x. 2 root root 4096 9月 23 2011 games |
| drwxr-xr-x. 2 root root 4096 9月 23 2011 include |
| drwxr-xr-x. 2 root root 4096 9月 23 2011 lib |
| drwxr-xr-x. 2 root root 4096 9月 23 2011 lib64 |
| drwxr-xr-x. 2 root root 4096 9月 23 2011 libexec |
| drwxr-xr-x. 12 root root 4096 8月 16 14:24 mariadb-5.5.43-linux-x86_64 |
| lrwxrwxrwx. 1 root root 27 8月 16 14:45 mysql -> mariadb-5.5.43-linux-x86_64 |
| drwxr-xr-x. 2 root root 4096 9月 23 2011 sbin |
| drwxr-xr-x. 5 root root 4096 8月 15 15:39 share |
| drwxr-xr-x. 2 root root 4096 9月 23 2011 src |
| |
修改mysql目录下的属主和属组
这里的属主和属组比较古怪,有的是root有的时500,这是为了适用于centos6的组织和编译
| |
| [root@localhost local] |
| |
| |
| [root@localhost mysql] |
| |
| |
| |
| |
| |
| [root@localhost mysql] |
| 总用量 220 |
| drwxr-xr-x. 2 root mysql 4096 8月 16 14:24 bin |
| -rw-r--r--. 1 root mysql 17987 4月 30 2015 COPYING |
| -rw-r--r--. 1 root mysql 26545 4月 30 2015 COPYING.LESSER |
| drwxr-xr-x. 3 root mysql 4096 8月 16 14:24 data |
| -rw-r--r--. 1 root mysql 8245 4月 30 2015 EXCEPTIONS-CLIENT |
| drwxr-xr-x. 3 root mysql 4096 8月 16 14:24 include |
| -rw-r--r--. 1 root mysql 8694 4月 30 2015 INSTALL-BINARY |
| drwxr-xr-x. 3 root mysql 4096 8月 16 14:24 lib |
| drwxr-xr-x. 4 root mysql 4096 8月 16 14:24 man |
| drwxr-xr-x. 11 root mysql 4096 8月 16 14:24 mysql-test |
| -rw-r--r--. 1 root mysql 108813 4月 30 2015 README |
| drwxr-xr-x. 2 root mysql 4096 8月 16 14:24 scripts |
| drwxr-xr-x. 27 root mysql 4096 8月 16 14:24 share |
| drwxr-xr-x. 4 root mysql 4096 8月 16 14:24 sql-bench |
| drwxr-xr-x. 3 root mysql 4096 8月 16 14:24 support-files |
| |
将data目录挂载到单独磁盘设备
data目录是存放mysql数据库中真正要存放数据的,不应当存放在目录的安装位置,应该有一个挂载的设备来存储mysql数据,因为mysql数据库有可能将来产生的数据量非常大
对磁盘做分区格式化
Linux fdisk是一个创建和维护分区表的程序,它兼容DOS类型的分区表、BSD或者SUN类型的磁盘列表
| [root@localhost mysql] |
| Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel |
| Building a new DOS disklabel with disk identifier 0x3f65542c. |
| Changes will remain in memory only, until you decide to write them. |
| After that, of course, the previous content won't be recoverable. |
| |
| Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite) |
| |
| The device presents a logical sector size that is smaller than |
| the physical sector size. Aligning to a physical sector (or optimal |
| I/O) size boundary is recommended, or performance may be impacted. |
| |
| WARNING: DOS-compatible mode is deprecated. It's strongly recommended to |
| switch off the mode (command 'c') and change display units to |
| sectors (command 'u'). |
| |
| Command (m for help): p |
| |
| Disk /dev/sdb: 136.4 GB, 136365211648 bytes |
| 255 heads, 63 sectors/track, 16578 cylinders |
| Units = cylinders of 16065 * 512 = 8225280 bytes |
| Sector size (logical/physical): 512 bytes / 4096 bytes |
| I/O size (minimum/optimal): 4096 bytes / 4096 bytes |
| Disk identifier: 0x3f65542c |
| |
| Device Boot Start End Blocks Id System |
| |
| Command (m for help): n |
| Command action |
| e extended |
| p primary partition (1-4) |
| p |
| Partition number (1-4): 1 |
| First cylinder (1-16578, default 1): (起始柱面,默认上一个分区结尾,这里默认是1) |
| Using default value 1 |
| Last cylinder, +cylinders or +size{K,M,G} (1-16578, default 16578): +20G (给他20G大小) |
| |
| Command (m for help): w |
| The partition table has been altered! |
| |
| Calling ioctl() to re-read partition table. |
| Syncing disks. |
| ---------------------------------------------- |
| |
| WARNING: Re-reading the partition table failed with error 16: 设备或资源忙. |
| 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. |
| (警告:重新读取分区表失败,错误16:设备或资源忙。 |
| 内核仍然使用旧表。 新表将在下次重新启动时或在运行partprobe(8)或kpartx(8)之后使用) |
| |
| [root@localhost mysql] |
| BLKPG: Device or resource busy |
| error adding partition 1 |
| BLKPG: Device or resource busy |
| error adding partition 2 |
| |
| |
| |
| |
| |
| |
| |
| |
调整新分区类型
| [root@localhost mysql] |
| |
| The device presents a logical sector size that is smaller than |
| the physical sector size. Aligning to a physical sector (or optimal |
| I/O) size boundary is recommended, or performance may be impacted. |
| |
| WARNING: DOS-compatible mode is deprecated. It's strongly recommended to |
| switch off the mode (command 'c') and change display units to |
| sectors (command 'u'). |
| |
| Command (m for help): t |
| Selected partition 1 |
| Hex code (type L to list codes): 8e |
| Changed system type of partition 1 to 8e (Linux LVM) |
| |
| Command (m for help): w |
| The partition table has been altered! |
| |
| Calling ioctl() to re-read partition table. |
| Syncing disks. |
| |
| |
| # t 更改分区的系统ID |
| # Hex code (type L to list codes): 8e (十六进制代码(从L到列表代码):8e) |
| # w 将表写入磁盘并退出 |
| # partx -a partx重读磁盘分区信息(如果有警告partx -a /dev/sdb多执行两遍) |
| |
创建逻辑卷
| |
| [root@localhost mysql] |
| Physical volume "/dev/sdb1" successfully created |
| |
| |
| |
| |
| |
| [root@localhost mysql] |
| Volume group "myvg" successfully created |
| |
| |
| |
| |
| [root@localhost mysql] |
| Logical volume "mydata" created |
| |
| |
| |
| |
| |
| |
| |
| [root@localhost mysql] |
| PV VG Fmt Attr PSize PFree |
| /dev/sda2 VolGroup lvm2 a-- 126.51g 0 |
| /dev/sdb1 myvg lvm2 a-- 20.01g 10.01g |
| |
| |
| |
| [root@localhost mysql] |
| VG |
| VolGroup 1 3 0 wz--n- 126.51g 0 |
| myvg 1 0 0 wz--n- 20.01g 20.01g |
| |
| |
| [root@localhost mysql] |
| LV VG Attr LSize Pool Origin Data% Move Log Cpy%Sync Convert |
| lv_home VolGroup -wi-ao---- 74.54g |
| lv_root VolGroup -wi-ao---- 50.00g |
| lv_swap VolGroup -wi-ao---- 1.97g |
| mydata myvg -wi-a----- 10.00g |
格式化
可以使用ext4,这里使用xfs文件系统,比ext4有更好的扩展性
centos6 安装xfs
| [root@localhost mysql] |
| |
| 用于自动处理可载入模块 |
| [root@localhost mysql] |
| |
| 安装后查看内核中是否有xfs模块 |
| [root@localhost mysql] |
| filename: /lib/modules/2.6.32-431.el6.x86_64/kernel/fs/xfs/xfs.ko |
| license: GPL |
| description: SGI XFS with ACLs, security attributes, large block/inode numbers, no debug enabled |
| author: Silicon Graphics, Inc. |
| srcversion: 6AF9EC31B1CDA34E78FB85F |
| depends: exportfs |
| vermagic: 2.6.32-431.el6.x86_64 SMP mod_unload modversions |
格式化
| [root@localhost mysql] |
| meta-data=/dev/myvg/mydata isize=256 agcount=16, agsize=163840 blks |
| = sectsz=4096 attr=2, projid32bit=0 |
| data = bsize=4096 blocks=2621440, imaxpct=25 |
| = sunit=0 swidth=0 blks |
| naming =version 2 bsize=4096 ascii-ci=0 |
| log =internal log bsize=4096 blocks=2560, version=2 |
| = sectsz=4096 sunit=1 blks, lazy-count=1 |
| realtime =none extsz=4096 blocks=0, rtextents=0 |
| |
开机自动挂载格式化完的磁盘设备
挂载到/mydata目录
创建/mydata目录
编辑/etc/fstab开机自动挂载
| |
| [root@localhost mysql] |
| |
| |
| |
| |
| |
| |
| |
| /dev/mapper/VolGroup-lv_root / ext4 defaults 1 1 |
| UUID=4c338374-215a-4081-a3b1-54f71eb54ae4 /boot ext4 defaults 1 2 |
| /dev/mapper/VolGroup-lv_home /home ext4 defaults 1 2 |
| /dev/mapper/VolGroup-lv_swap swap swap defaults 0 0 |
| tmpfs /dev/shm tmpfs defaults 0 0 |
| devpts /dev/pts devpts gid=5,mode=620 0 0 |
| sysfs /sys sysfs defaults 0 0 |
| proc /proc proc defaults 0 0 |
| /dev/myvg/mydata /mydata xfs defaults 0 0 |
| |
| |
| |
挂载
| |
| [root@localhost mysql] |
| [root@localhost mysql] |
| /dev/mapper/VolGroup-lv_root on / type ext4 (rw) |
| proc on /proc type proc (rw) |
| sysfs on /sys type sysfs (rw) |
| devpts on /dev/pts type devpts (rw,gid=5,mode=620) |
| tmpfs on /dev/shm type tmpfs (rw,rootcontext="system_u:object_r:tmpfs_t:s0") |
| /dev/sda1 on /boot type ext4 (rw) |
| /dev/mapper/VolGroup-lv_home on /home type ext4 (rw) |
| none on /proc/sys/fs/binfmt_misc type binfmt_misc (rw) |
| /dev/mapper/myvg-mydata on /mydata type xfs (rw) |
| |
| |
| |
| |
生成mysql元数据数据库
创建目录并修改属组属主
为了将来组织方便可以在/mydata目录上创建一个目录data,以后有data目录来存放mysql数据
data目录的属组属主应该是mysql
因为mysql运行mysql服务的进程是mysql用户他要些数据放进data目录,所以data目录需要mysql用户权限
| |
| [root@localhost mydata] |
| |
| |
| [root@localhost mydata] |
| 总用量 0 |
| drwxr-xr-x. 2 mysql mysql 6 8月 16 17:35 data |
| |
安装mysql元数据数据库
mysql启动起来后mysql里面就有4个mysql数据库,其中里面最关键的一个就叫做mysql数据库,它是用来存放mysql数据库的元数据的,比如当前数据库中有多少用户,每个用户有什么权限,有多少表,表名字是什么,有多少字段,字段名字是什么,等等等都应该指定的,这个数据库并不是凭空来的需要自行使用脚本生成他
切换目录
生成mysql元数据数据库,也叫初始化数据库,这个脚本只能在这个目录下运行(执行完,数据库基本安装就完成了)
| |
| [root@localhost mysql] |
| |
| |
| [root@localhost mysql] |
| aria_log.00000001 aria_log_control mysql performance_schema test |
| |
| |
| |
| |
| [root@localhost mysql] |
| |
| |
| |
| |
| |
| |
启动mysql
启动mysql需要服务脚本,mysql已经提供
切换目录
复制目录(将mysql.server服务脚本复制到/etc/rc.d/init.d/mysqld目录下)
添加服务
| |
| [root@localhost mysql] |
| |
| |
| [root@localhost mysql] |
| mysqld 0:关闭 1:关闭 2:启用 3:启用 4:启用 5:启用 6:关闭 |
| |
| |
| |
| |
| |
| |
| |
| |
mysql配置文件
| 配置格式:类ini格式,为各程序均通过单个配置文件提供配置信息 |
| [progname] |
| 配置文件查找次序:(第一个找完第二个一定会找,最后读到了反而时最终生效的,重复的生效最后的,越是后找到了越是最终生效的) |
| /etc/my.cnf --> /etc/mysql/my.cnf --> --default-extra-files/PATH/TO/CONFFILE(生成mysql元数据数据库时这个选项指明的路径找这个文件) --> ~/.my.cnf(运行当前程序的用户的家目录下的隐藏的.my.cnf配置文件) |
避免冲突,有意组织到/etc/mysql/my.cnf这个目录下
创建目录
复制配置文件
| |
| [root@localhost mysql] |
| |
| |
| [root@localhost mysql] |
| /usr/local/mysql |
| |
| [root@localhost mysql] |
| binary-configure my-huge.cnf my-large.cnf my-small.cnf mysql-log-rotate SELinux |
| magic my-innodb-heavy-4G.cnf my-medium.cnf mysqld_multi.server mysql.server |
| |
| my-small.cnf 最小配置安装,内存⇐64M,数据数量最少( 小 是为了小型数据库而设计的。不应该把这个模型用于含有一些常用项目的数据库) |
| my-large.cnf 内存=512M(大 为专用于一个SQL数据库的计算机而设计的) |
| my-medium.cnf 32M<内存<64M,或者内存有128M,但是数据库与web服务器公用内存(中 为中等规模的数据库而设计的。) |
| my-huge.cnf 1G<内存<2G,服务器主要运行mysql(巨大 是为企业中的数据库而设计的,专门数据库服务器) |
| my-innodb-heavy-4G.cnf 最大配置安装,内存至少4G |
修改配置文件
大多数默认配置时不符合我们需要的,比如data目录是数据库的安装路径,我们需要改一下
| |
| [root@localhost mysql] |
| |
| datadir = /mydata/data |
| innodb_file_per_table = on |
| skip_name_resolve = on |
| |
| [client]配置段(mysql客户端程序) |
| [mysqld]配置段(mysqld服务配置使用这段) |
| port = 3306 (监听的端口) |
| socket = /tmp/mysql.sock(提供的套接字文件) |
| thread_concurrency = 8(并行的线程数,这个数量应该是cpu核心数量乘以2) |
| datadir = /mydata/data (特别关键,添加一条,重新指定数据库路径) |
| |
| innodb_file_per_table = on(在MySQL的配置文件[mysqld]部分,增加innodb_file_per_table参数,可以修改InnoDB为独立表空间模式,每个数据库的每个表都会生成一个数据空间。) |
| |
| skip_name_resolve = on (跳过解析(跳过地址反解)) |
| |
| |
innodb_file_per_table = on
| innodb_file_per_table |
| MySQL InnoDB引擎 默认会将所有的数据库InnoDB引擎的表数据存储在一个共享空间中:ibdata1,当增删数据库的时候,ibdata1文件不会自动收缩,单个数据库的备份也将成为问题。通常只能将数据使用mysqldump 导出,然后再导入解决这个问题。 |
| |
| 在MySQL的配置文件[mysqld]部分,增加innodb_file_per_table参数,可以修改InnoDB为独立表空间模式,每个数据库的每个表都会生成一个数据空间。 |
| |
| |
| |
| 注意: |
| |
| 如果启用了innodb_file_per_talbe参数,需要注意的是每张表的表空间内存放的只是数据、索引和插入缓冲Bitmap页,其他数据如:回滚信息、插入缓冲索引页、系统事物信息、二次写缓冲(Double write buffer)等还是放在原来的共享表空间内。同时说明了一个问题:即使启用了innodb_file_per_table参数共享表空间还是会不断的增加其大小的。 |
| |
| 独立表空间优缺点: |
| 优点: |
| 1:每个表的数据、索引存放在自己单独的表空间中。 |
| 2:空间可以回收(drop/truncate table 方式操作表空间不能自动回收) |
| 3:对于独立的表空间、碎片影响的性能要低于共享表空间 |
| 缺点: |
| 单表增加比共享表空间方式更大 |
| |
| 结论: |
| 共享表空间在Insert操作上有一些优势,但在其它都没独立表空间表现好。 |
| 当启用独立表空间时,请合理调整一下 innodb_open_files 参数。 |
| |
skip_name_resolve = on
| mysql连接很慢,登陆到服务器上查看服务器日志都是正常的,无可疑记录,登陆到mysql服务器上,查看下进程,发现有很多这样的连接: |
| 218 | unauthenticated user | 192.168.10.6:44500 | NULL | Connect | NULL | login | NULL |
| 219 | unauthenticated user | 192.168.10.6:44501 | NULL | Connect | NULL | login | NULL |
| ........ |
| |
| |
| 原因是由于mysql对连接的客户端进行DNS反向解析。 |
| 有2种解决办法: |
| 1,把client的ip写在mysql服务器的/etc/hosts文件里,随便给个名字就可以了。 |
| 2,在 my.cnf 中加入 –skip-name-resolve 。 |
| 对于第一种方法比较笨,也不实用,那么 skip-name-resolve 选项可以禁用dns解析,但是,这样不能在mysql的授权表中使用主机名了,只能使用IP。 |
| 我理解mysql是这样来处理客户端解析过程的, |
| 1,当mysql的client连过来的时候,服务器会主动去查client的域名。 |
| 2,首先查找 /etc/hosts 文件,搜索域名和IP的对应关系。 |
| 3,如果hosts文件没有,则查找DNS设置,如果没有设置DNS服务器,会立刻返回失败,就相当于mysql设置了skip-name-resolve参数,如果设置了DNS服务器,就进行反向解析,直到timeout。 |
| |
| |
| |
| 所谓反向解析是这样的: |
| mysql接收到连接请求后,获得的是客户端的ip,为了更好的匹配mysql.user里的权限记录(某些是用hostname定义的)。 |
| 如果mysql服务器设置了dns服务器,并且客户端ip在dns上并没有相应的hostname,那么这个过程很慢,导致连接等待。 |
| |
| 添加skip-name-resolve以后就跳过着一个过程了。 |
| |
启动服务
| |
| [root@localhost mysql] |
| Starting MySQL.. [确定] |
| |
安装后的设定(三步)
(1)为所有root用户设定密码(常用有三种方法):
| |
| SET PASSWORD; |
| |
| |
| updata mysql.user SET password(字段等于PASSWORD()函数里面夹着要设置的密码)=PASSWORD('密码串') WHERE(加条件) user=root(用户名等于谁否者修改整张表) |
| |
| |
| |
| |
(2)删除所有匿名用户:
| mysql> DROP USER ''@'localhost'; |
| |
上述两步骤可运行命令:mysql_secure_installation来实现
(3)建议关闭主机名反解功能:
连入mysql服务器
mysql客服端没有加入PASH路径环境变量
切换目录
连接mysql服务端
用户账号: 'user@host'
| 对于mysql用户账号有两部分组成 |
| |
| user:用户各 |
| host:host是当前主机的ip地址,此用户访间mysqld服务时允许通过哪些主机远程创建连接;因此就需要用户访问限制不仅从用户名限制,还要从所能使用的客户端主机进行限制 |
| host可以使用:IP、网络地址、主机名、通配符(%和_用来匹配任意长度的字符和任意单个字符),mysqld服务器端在验证客户端身份时会尝试在每一个客户端创建连接时反解客户端的ip地址为主机名,根据主机名在检查权限,有时候我们明明做了授权却发现没法访问,那是有可能根据主机名做了授权,但是我们本地服务器却无法反解对方的ip地址到主机名 因此建议检查主机名my.cnf, [mysqld] skip_name_resolve = yes 跳过主机名解析; |
本地连接mysql服务端
| [root@localhost bin] |
| Welcome to the MariaDB monitor. Commands end with ; or \g. |
| Your MariaDB connection id is 3 |
| Server version: 5.5.43-MariaDB-log MariaDB Server |
| |
| Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others. |
| |
| Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. |
| |
| MariaDB [(none)]> |
| |
| |
安全的初始化
查看mysql内置用户
| MariaDB [(none)]> use mysql; |
| Database changed |
| MariaDB [mysql]> select user,host,password from user; |
| +------+-----------------------+----------+ |
| | user | host | password | |
| +------+-----------------------+----------+ |
| | root | localhost | | |
| | root | localhost.localdomain | | |
| | root | 127.0.0.1 | | |
| | root | ::1 | | |
| | | localhost | | |
| | | localhost.localdomain | | |
| +------+-----------------------+----------+ |
| 6 rows in set (0.00 sec) |
| |
| |
| |
| |
| |
| |
| |
进行安全初始化
| [root@localhost bin] |
| mysql_secure_installation mysql_setpermission |
| [root@localhost bin] |
| /usr/local/mysql/bin/mysql_secure_installation: line 379: find_mysql_client: command not found |
| |
| NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB |
| SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY! |
| |
| In order to log into MariaDB to secure it, we'll need the current |
| password for the root user. If you've just installed MariaDB, and |
| you haven't set the root password yet, the password will be blank, |
| so you should just press enter here. |
| |
| Enter current password for root (enter for none): |
| (输入root用户的当前密码(不输入密码):) 没有回车 |
| OK, successfully used password, moving on... |
| |
| Setting the root password ensures that nobody can log into the MariaDB |
| root user without the proper authorisation. |
| |
| Set root password? [Y/n] y |
| (设置root密码? [是/否]) 是 |
| New password: |
| Re-enter new password: |
| Password updated successfully! |
| Reloading privilege tables.. |
| ... Success! |
| |
| |
| 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. |
| |
| Remove anonymous users? [Y/n] y |
| (删除匿名用户? [是/否])是 |
| ... Success! |
| |
| 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? [Y/n] n |
| (禁止远程root登录? [是/否] 否 (按道理来讲安全起见无论如何都要禁止管理员观察登陆,管理员权限太大了,可以清空所有数据库,测试用为了方便先不禁用)) |
| |
| ... skipping. |
| |
| 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. |
| |
| Remove test database and access to it? [Y/n] n |
| (删除测试数据库并访问它? [是/否] 否 (测试库要不要对我们风险并不大,将来对mysql做设置操作时,测试性能还会用到,可以留着)) |
| ... skipping. |
| |
| Reloading the privilege tables will ensure that all changes made so far |
| will take effect immediately. |
| |
| Reload privilege tables now? [Y/n] y |
| (现在重新加载特权表? [是/否] 是 (一般我们改了任何用户和密码,或者改了授权都应该重载的)) |
| ... Success! |
| |
| Cleaning up... |
| |
| All done! If you've completed all of the above steps, your MariaDB |
| installation should now be secure. |
| |
| Thanks for using MariaDB! |
| |
进行安全初始化后在连接数据库
| |
| |
| [root@localhost bin] |
| ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) |
| |
| |
| |
| [root@localhost bin] |
| Enter password: |
| Welcome to the MariaDB monitor. Commands end with ; or \g. |
| Your MariaDB connection id is 14 |
| Server version: 5.5.43-MariaDB-log MariaDB Server |
| |
| Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others. |
| |
| Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. |
| |
| MariaDB [(none)]> use mysql; |
| Database changed |
| MariaDB [mysql]> select user,host,password from user; |
| +------+-----------+-------------------------------------------+ |
| | user | host | password | |
| +------+-----------+-------------------------------------------+ |
| | root | localhost | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B | |
| | root | 127.0.0.1 | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B | |
| | root | ::1 | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B | |
| +------+-----------+-------------------------------------------+ |
| 3 rows in set (0.00 sec) |
| |
MariaDB的程序组成
用通用二进制程序包可以看到他的全貌
| [root@localhost ~] |
| aria_chk mysqlbug mysql_plugin |
| aria_dump_log mysqlcheck mysql_secure_installation |
| aria_ftdump mysql_client_test mysql_setpermission |
| aria_pack mysql_client_test_embedded mysqlshow |
| aria_read_log mysql_config mysqlslap |
| innochecksum mysql_convert_table_format mysqltest |
| msql2mysql mysqld mysqltest_embedded |
| myisamchk mysqld_multi mysql_tzinfo_to_sql |
| myisam_ftdump mysqld_safe mysql_upgrade |
| myisamlog mysqldump mysql_waitpid |
| myisampack mysqldumpslow mysql_zap |
| my_print_defaults mysql_embedded mytop |
| mysql mysql_find_rows perror |
| mysqlaccess mysql_fix_extensions replace |
| mysqladmin mysqlhotcopy resolveip |
| mysqlbinlog mysqlimport resolve_stack_dump |
| |
| |
服务器端程序
mysqld 这是我们服务器端程序,但我们服务器真正运行的是mysqld_safe(线程安全的mysql)
| |
| [root@localhost ~] |
| root 3385 0.0 0.1 11472 1404 pts/0 S 20:05 0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/mydata/data --pid-file=/mydata/data/localhost.localdomain.pid |
| mysql 3786 0.0 10.8 834584 126380 pts/0 Sl 20:05 0:04 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/mydata/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/var/log/mysqld.log --pid-file=/mydata/data/localhost.localdomain.pid --socket=/tmp/mysql.sock --port=3306 |
| root 12782 0.0 0.0 103256 840 pts/2 S+ 22:16 0:00 grep mysql |
mysqld_multi (多实例的mysql)mysql也可以一个服务器上运行多个mysql进程但他们使用不同的端口
| 服务器监听的两种socket(套接字)地址: |
| ip socket:监听在tcp的3306端口,支持远程通信: |
| unix sock:监听在sock文件上(/tmp/sysql.sock, /var/lib/mysql/mysql.sock),仅支持本地通信:(基于socket文件通信就类似共享内存一样大家不用通过tcp/ip协议栈,整个封包和解包速度更快,mysql客户端连接mysql服务器时如果使用unix sock必需连的server地址只能是localhost或127.0.0.1,如果使用localhost会自动通过unix sock进行通信) |
| server: localhost, 127.0.0.1 |
客户端程序
mysql 命令行式客户端(专用的交互式客户端工具)
mysqladmin 远程管理工具
mysqlbinlog 二进制日志管理工具
| 命令行交互式客户端程序: mysql |
| mysql(如果不跟任何选项表示连接本机的127.0.0.1unix sock文件,用户为root密码为空) |
| -uUSERNAME:用户名:默认为root |
| -hHOST:服务器主机;默认为localhost |
| -pPASSWORD:用户的密码;1默认为空密码 |
| |
| 注意: mysql用户账号由两部分组成: 'USERNAME'@'HOST';其中HOST用于限制此用户可通过哪些主机远程连接mysql服务;(这表示这个用户仅能够通过 这个主机远程连接到mysql服务器); |
| |
| 支持使用通配符: |
| |
| 比如:172.16.0.0/16可以使用: 172.16. |
| _:匹配任意单个字符: |
命令
客户端命令:本地执行
help 获取所有客户端命令
| MariaDB [mysql]> help |
| |
| General information about MariaDB can be found at |
| http://mariadb.org |
| |
| List of all MySQL commands: |
| Note that all text commands must be first on line and end with ';' |
| ? (\?) Synonym for `help'. |
| clear (\c) Clear the current input statement. |
| connect (\r) Reconnect to the server. Optional arguments are db and host. |
| delimiter (\d) Set statement delimiter. |
| edit (\e) Edit command with $EDITOR. |
| ego (\G) Send command to mysql server, display result vertically. |
| exit (\q) Exit mysql. Same as quit. |
| go (\g) Send command to mysql server. |
| help (\h) Display this help. |
| nopager (\n) Disable pager, print to stdout. |
| notee (\t) Don't write into outfile. |
| pager (\P) Set PAGER [to_pager]. Print the query results via PAGER. |
| print (\p) Print current command. |
| prompt (\R) Change your mysql prompt. |
| quit (\q) Quit mysql. |
| rehash (\ |
| source (\.) Execute an SQL script file. Takes a file name as an argument. |
| status (\s) Get status information from the server. |
| system (\!) Execute a system shell command. |
| tee (\T) Set outfile [to_outfile]. Append everything into given outfile. |
| use (\u) Use another database. Takes database name as argument. |
| charset (\C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets. |
| warnings (\W) Show warnings after every statement. |
| nowarning (\w) Don't show warnings after every statement. |
| |
| For server side help, type 'help contents' |
| |
| # 每一个命令都有两种形式,第一status 名字,第二(\s)简写格式 |
| |
status 从服务器获取状态信息。
| MariaDB [mysql]> \s |
| 或 |
| MariaDB [mysql]> status |
| -------------- |
| ./mysql Ver 15.1 Distrib 5.5.43-MariaDB, for Linux (x86_64) using readline 5.1 |
| |
| Connection id: 14 |
| Current database: mysql |
| Current user: root@localhost |
| SSL: Not in use |
| Current pager: stdout |
| Using outfile: '' |
| Using delimiter: ; |
| Server: MariaDB |
| Server version: 5.5.43-MariaDB-log MariaDB Server (服务器端mariadb的版本号) |
| Protocol version: 10 |
| Connection: Localhost via UNIX socket (连接时使用的是UNIX socket的连接方式) |
| Server characterset: latin1 |
| Db characterset: latin1 |
| Client characterset: utf8 |
| Conn. characterset: utf8 |
| UNIX socket: /tmp/mysql.sock (UNIX socket使用的文件) |
| Uptime: 3 hours 56 min 28 sec |
| |
| Threads: 1 Questions: 46 Slow queries: 0 Opens: 0 Flush tables: 2 Open tables: 26 Queries per second avg: 0.003 |
| (Threads: 1 当前服务器端有几个线程) |
| (Questions: 46 完成了多个查询操作了) |
| (Slow queries: 0 慢查询数) |
| ( Opens: 0 打开了多少个文件) |
| (Flush tables: 2 刷写了多少张表(刷新表(清除缓存))) |
| (Open tables: 26 打开了多少张表) |
| (Queries per second avg: 0.003 查询的平均时间) |
服务端命令:通过mysql协议发往服务器执行并取回结果
每个命令都必须命令结束符号:默认为分号:
看当前服务器版本
| |
| MariaDB [mysql]> select version(); |
| +--------------------+ |
| | version() | |
| +--------------------+ |
| | 5.5.43-MariaDB-log | |
| +--------------------+ |
| 1 row in set (0.00 sec) |
| |
使用yum安装mariadb
查看yum源数据库安装包版本
| [root@bogon ~] |
| CentOS-8 - AppStream 2.0 kB/s | 4.3 kB 00:02 |
| CentOS-8 - Base 4.0 kB/s | 3.9 kB 00:00 |
| CentOS-8 - Extras 1.5 kB/s | 1.5 kB 00:00 |
| 可安装的软件包 |
| 名称 : mysql-server |
| 版本 : 8.0.17 |
| 发布 : 3.module_el8.0.0+181+899d6349 |
| 架构 : x86_64 |
| 大小 : 22 M |
| 源 : mysql-8.0.17-3.module_el8.0.0+181+899d6349.src.rpm |
| 仓库 : AppStream |
| 概况 : The MySQL server and related files |
| URL : http://www.mysql.com |
| 协议 : GPLv2 with exceptions and LGPLv2 and BSD |
| 描述 : MySQL is a multi-user, multi-threaded SQL database server. MySQL is a |
| : client/server implementation consisting of a server daemon (mysqld) |
| : and many different client programs and libraries. This package contains |
| : the MySQL server and some accompanying files and directories. |
| |
| [root@bogon ~] |
| 上次元数据过期检查:0:00:21 前,执行于 2020年08月14日 星期五 18时57分18秒。 |
| 已安装的软件包 |
| 名称 : mariadb-server |
| 时期 : 3 |
| 版本 : 10.3.17 |
| 发布 : 1.module_el8.1.0+257+48736ea6 |
| 架构 : x86_64 |
| 大小 : 88 M |
| 源 : mariadb-10.3.17-1.module_el8.1.0+257+48736ea6.src.rpm |
| 仓库 : @System |
| 来自仓库 : AppStream |
| 概况 : The MariaDB server and related files |
| URL : http://mariadb.org |
| 协议 : GPLv2 with exceptions and LGPLv2 and BSD |
| 描述 : MariaDB is a multi-user, multi-threaded SQL database server. It is a |
| : client/server implementation consisting of a server daemon (mysqld) |
| : and many different client programs and libraries. This package contains |
| : the MariaDB server and some accompanying files and directories. |
| : MariaDB is a community developed branch of MySQL. |
| |
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· DeepSeek 解答了困扰我五年的技术问题
· 为什么说在企业级应用开发中,后端往往是效率杀手?
· 用 C# 插值字符串处理器写一个 sscanf
· Java 中堆内存和栈内存上的数据分布和特点
· 开发中对象命名的一点思考
· 为什么说在企业级应用开发中,后端往往是效率杀手?
· DeepSeek 解答了困扰我五年的技术问题。时代确实变了!
· 本地部署DeepSeek后,没有好看的交互界面怎么行!
· 趁着过年的时候手搓了一个低代码框架
· 推荐一个DeepSeek 大模型的免费 API 项目!兼容OpenAI接口!