数据读写分离
MySQL读写分离
概念:把客户端查询数据的请求和存储数据的SQL命令,分别给不同的数据库服务器处理
读写分离的原理
- 由MySQL代理面向客户端提供服务
- 收到SQL的写请求时,交给master服务器处理
- 收到SQL的读请求时,交给slave服务器处理
读写分离拓扑图
部署maxscale服务
Maxscale代理软件
构建MySQL读写分离
- 部署MySQL一主一从结构
- 主服务器 192.168.4.51
- 从服务器 192.168.4.52
- 部署MySQL代理服务器
- 装包、修改配置文件、启动服务
- 测试配置
- 客户端连接代理服务访问数据
1.配置MySQL主从同步:host52数据库服务器作为host51的从服务器
host51主机:
[root@host51 ~]# vim /etc/my.cnf
[mysqld]
server_id=51 #指定服务器id号
log_bin=master51 #启用binlog日志,并指定文件名前缀
[root@host53 mysql]# systemctl restart mysqld
[root@host51 ~]# mysql -uroot -p123456
mysql> grant replication slave on *.* to repluser@"%" identified by "123qqq...A";
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> show master status;
+-----------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------+----------+--------------+------------------+-------------------+
| master51.000003 | 365 | | | |
+-----------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
host52主机:
[root@host52 ~]# vim /etc/my.cnf
[mysqld]
server_id=52
[root@host54 ~]# systemctl restart mysqld
[root@host52 ~]# mysql -uroot -p123456
mysql> change master to
-> master_host="192.168.4.51",
-> master_user="repluser",
-> master_password="123qqq...A",
-> master_log_file="master51.000003",
-> master_log_pos=365;
Query OK, 0 rows affected, 2 warnings (0.08 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.4.51
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master51.000003
Read_Master_Log_Pos: 154
Relay_Log_File: host52-relay-bin.000011
Relay_Log_Pos: 365
Relay_Master_Log_File: master51.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 154
Relay_Log_Space: 6474
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 51
Master_UUID: 6d7b632c-4e3f-11ea-8a8d-000c29875030
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
此时做初步测试:
首先在host51主机上查看授权用户的权限
mysql> show grants for repluser@"%";
+--------------------------------------------------+
| Grants for repluser@% |
+--------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'%' |
+--------------------------------------------------+
1 row in set (0.00 sec)
mysql> create database aaa;
Query OK, 1 row affected (0.06 sec)
mysql> create table aaa.b(name char(10));
Query OK, 0 rows affected (0.24 sec)
mysql> insert into aaa.b values("zzz");
Query OK, 1 row affected (0.11 sec)
mysql> select * from aaa.b;
+------+
| name |
+------+
| zzz |
+------+
1 row in set (0.00 sec)
在host52主机上连接数据库服务器,查看是否有刚刚写入的数据
mysql> select * from aaa.b;
+------+
| name |
+------+
| zzz |
+------+
1 row in set (0.00 sec)
*****
环境要求:关闭防火墙,保证yum源可以正常使用,安装提供服务的软件
配置读写分离服务器192.168.4.57,具体操作如下:
1.安装软件maxscale-2.1.2-1.rhel.7.x86_64.rpm
[root@host57 ~]# rpm -ivh maxscale-2.1.2-1.rhel.7.x86_64.rpm #安装maxscale
提供服务程序和管理命令
[root@host57 ~]# max [Tab][Tab] #查看maxscale的命令
maxadmin maxbinlogcheck maxpasswd
maxavrocheck maxkeys maxscale
2.修改配置文件
[root@host57 ~]# ls /etc/maxscale.cnf
/etc/maxscale.cnf
[root@host57 ~]# cp /etc/maxscale.cnf /root
[root@host57 ~]# vim /etc/maxscale.cnf
9 [maxscale] #定义线程个数
10 threads=auto #改为自动模式
...
18 [server1] #定义数据库服务器
19 type=server
20 address=192.168.4.51 #master主机IP地址
21 port=3306
22 protocol=MySQLBackend
23
24 [server2] #定义数据库服务器2
25 type=server
26 address=192.168.4.52 #slave主机IP地址
27 port=3306
28 protocol=MySQLBackend
35 [MySQL Monitor] #定义数据库监视的数据库节点
36 type=monitor
37 module=mysqlmon
38 servers=server1,server2 #监控的数据库列表,不能写IP
39 user=maxscalemon #监控用户
40 passwd=123qqq...A #密码
41 monitor_interval=10000
52 #[Read-Only Service] #第52行到58行注释掉,取消定义只读服务
53 #type=service
54 #router=readconnroute
55 #servers=server1
56 #user=myuser
57 #passwd=mypwd
58 #router_options=slave
63 [Read-Write Service] #定义读写分离数据库的节点
64 type=service
65 router=readwritesplit
66 servers=server1,server2 #主,从服务器主机名
67 user=maxscalerouter #监控路由用户
68 passwd=123qqq...A #密码
69 max_slave_connections=100%
75 [MaxAdmin Service] #定义管理服务
76 type=service
77 router=cli
85 #[Read-Only Listener] #取消定义只读服务的端口,注释掉
86 #type=listener
87 #service=Read-Only Service
88 #protocol=MySQLClient
89 #port=4008
91 [Read-Write Listener] #定义读写分离服务的端口号节点
92 type=listener
93 service=Read-Write Service
94 protocol=MySQLClient
95 port=4006
97 [MaxAdmin Listener] #定义管理服务的端口号节点
98 type=listener
99 service=MaxAdmin Service
100 protocol=maxscaled
101 socket=default
102 port=4016 #自己定义管理服务的端口号,范围0-65535,但建议不要设置在1024以内,1024以内的端口都已经有固定的服务
[root@host57 ~]# sed -i '/#/d' /etc/maxscale.cnf #可以将配置文件里的注释删除
3.添加授权用户
根据maxscale.cnf文件配置,在主、从服务器上添加对应的授权用户,因为两台数据库服务器是主从同步结构,只在主数据库服务器添加用户即可,从服务器会自动同步
mysql> grant replication slave ,replication client on *.* to maxscalemon@"%" identified by "123qqq...A"; #授权监控用户
Query OK, 0 rows affected, 1 warning (0.07 sec)
mysql> grant select on mysql.* to maxscalerouter@"%" identified by "123qqq...A"; #授权路由用户
Query OK, 0 rows affected, 1 warning (0.00 sec)
4.查看授权用户
mysql> select host , user from mysql.user where user in ("maxscalerouter","maxscalemon");
+------+----------------+
| host | user |
+------+----------------+
| % | maxscalemon |
| % | maxscalerouter |
+------+----------------+
2 rows in set (0.08 sec)
在代理服务器57主机上,测试授权用户
[root@host57 ~]#yum -y install mariadb //安装提供mysql命令的软件包
[root@host57 ~]# mysql -h 192.168.4.51 -umaxscalemon -p123qqq…A
[root@host57 ~]# mysql -h 192.168.4.52 -umaxscalemon -p123qqq…A
[root@host57 ~]# mysql -h 192.168.4.51 -umaxscalerouter -p123qqq…A
[root@host57 ~]#mysql -h 192.168.4.52 -umaxscalerouter -p123qqq…A
5.启动maxscale代理服务
[root@host57 ~]# maxscale -f /etc/maxscale.cnf
[root@host57 ~]# netstat -nutlp | grep maxscale #查看maxscale端口,4006为读写分离端口,4016为管理服务端口
tcp6 0 0 :::4006 :::* LISTEN 11249/maxscale
tcp6 0 0 :::4016 :::* LISTEN 11249/maxscale
[root@host57 ~]# ps -C maxscale #查看进程
PID TTY TIME CMD
11249 ? 00:00:00 maxscale
测试配置
1.查看监控信息(在主机57本机访问自己)
[root@host57 ~]# maxadmin -uadmin -pmariadb -P4016
MaxScale> list servers #只能有一个空格!!
Servers.
-------------------+-----------------+-------+-------------+--------------------
Server | Address | Port | Connections | Status
-------------------+-----------------+-------+-------------+--------------------
server1 | 192.168.4.51 | 3306 | 0 | Master, Running
server2 | 192.168.4.52 | 3306 | 0 | Slave, Running
-------------------+-----------------+-------+-------------+--------------------
2.在主服务器上添加授权连接用户
在主服务器上添加即可,从服务器会自动同步
[root@host50 ~]# which mysql
/usr/bin/mysql
[root@host50 ~]# rpm -q mariadb
mariadb-5.5.56-2.el7.x86_64
[root@host50 ~]# mysql -h192.168.4.58 -P4006 -ujim -p123qqq...A
ERROR 1045 (28000): Access denied for user 'jim'@'::ffff:192.168.4.50' (using password: YES)
#客户端访问服务器只能使用路由用户访问
在主服务器添加授权用户给客户端连接使用
客户端使用授权用户连接
mysql> create database db6;
Query OK, 1 row affected (0.00 sec)
mysql> create table db6.user(name char(15));
Query OK, 0 rows affected (0.06 sec)
mysql> grant select ,insert on db6.* to jim@"%" identified by "123qqq...A";
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> desc db6.user;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name | char(15) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> select user ,host from mysql.user;
+----------------+--------------+
| user | host |
+----------------+--------------+
| haha | % |
| jim | % |
| maxscalemon | % |
| maxscalerouter | % |
| repluser | % |
| webadmin | % |
| admin | 192.168.4.% |
| root | 192.168.4.52 |
| admin2 | localhost |
| mysql.sys | localhost |
| root | localhost |
| tian | localhost |
+----------------+--------------+
12 rows in set (0.00 sec)
在host52主机上面查看
mysql> desc db6.user;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name | char(15) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
1 row in set (0.00 sec)
在host50客户端访问代理服务器,插入数据可以成功查看
[root@host50 ~]# mysql -h192.168.4.58 -P4006 -ujim -p123qqq...A
MySQL [(none)]> insert into db6.user values("bob");
Query OK, 1 row affected (0.01 sec)
MySQL [(none)]> select * from db6.user;
+------+
| name |
+------+
| bob |
+------+
1 row in set (0.01 sec)
在host51以及host52主机也可以看到插入的数据
mysql> select * from db6.user;
+------+
| name |
+------+
| bob |
+------+
1 row in set (0.00 sec)
MySQL多实例
多实例概述
- 什么是多实例?
- 在一台物理主机上运行多个数据库服务
- 为什么要使用多实例?
- 节约运维成本
- 提高硬件利用率
配置多实例
环境准备:
在192.168.4.57主机上配置,要提前安装软件包mysql-5.7.20可以支持配置多实例
软件包在此!提取码:iptb
[root@host57 ~]# ls
mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz
[root@host57 ~]# tar -zxvf mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz
[root@host57 ~]# ls mysql-5.7.20-linux-glibc2.12-x86_64
bin COPYING docs include lib man README share support-files
[root@host57 ~]# mv mysql-5.7.20-linux-glibc2.12-x86_64 /usr/local/mysql
[root@host57 ~]# cd /usr/local/mysql
[root@host57 mysql]# ls
bin(SQL命令) COPYING docs(帮助文档,二进制) include(源码调用程序文件) lib(源码调用模块) man(帮助文档) README share(帮助文档) support-files(配置文件模板)
[root@host57 mysql]# PATH=/usr/local/mysql/bin:$PATH #将path变量赋新值
[root@host57 mysql]# echo $PATH
/usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin
[root@host57 mysql]# mysql[Tab][Tab]
mysql mysqld_multi mysql_secure_installation
mysqladmin mysqld_safe mysqlshow
mysqlbinlog mysqldump mysqlslap
mysqlcheck mysqldumpslow mysql_ssl_rsa_setup
mysql_client_test_embedded mysql_embedded mysqltest_embedded
mysql_config mysqlimport mysql_tzinfo_to_sql
mysql_config_editor mysql_install_db mysql_upgrade
mysqld mysql_plugin mysqlxtest
mysqld-debug mysqlpump
[root@host57 mysql]# vim /etc/bashrc #在文档结尾添加永久配置全局变量
export PATH=/usr/local/mysql/bin:$PATH
[root@host57 ~]# yum -y install libaio #安装依赖包
[root@host57 ~]# useradd mysql #添加进程所有者用户,一定不要忘记创建!!!!
创建并编写主配置文件 /etc/my.cnf
多实例服务的运行配置
如果原本有下载mariadb或者安装过mysql软件就会产生/etc/my.cnf,要先删除
[root@host57 ~]# rm -rf /etc/my.cnf
socket文件:当自己访问自己时,通过socket文件传递数据,是访问mysql接口程序的文件,套接字文件,虚拟文件,服务启动时有该文件,服务关闭时文件即消失
[root@host57 ~]# vim /etc/my.cnf
[mysqld_multi] #启用多实例
mysqld=/usr/local/mysql/bin/mysqld_safe #指定进程文件路径
mysqladmin=/usr/local/mysql/bin/mysqladminer=root #指定管理命令路径
user=root #指定进程用户
[mysqld1] #实例进程名称
datadir=/dir1 #数据库目录,要手动创建
log-error=/dir1/mysqld1.error #错误日志位置
socket=/dir1/mysqld1.sock #指定sock文件的路径和名称
pid-file=/dir1/mysqld1.pid #进程pid号文件位置
port=3307 #端口号(自定义,不能是已经存在的默认的端口)
[mysqld2]
datadir=/dir2
log-error=/dir2/mysqld2.error
socket=/dir2/mysqld2.sock
pid-file=/dir2/mysqld2.pid
port=3308
[root@host57 ~]# mkdir /dir1 #创建数据库目录
[root@host57 ~]# mkdir /dir2
启动服务(首次启动服务会创建root登录的初始密码)
[root@host57 ~]# ls /dir1
[root@host57 ~]# mysqld_multi start 1 #开启第一个服务
[root@host57 ~]# ss -nutlp | grep 3307
tcp LISTEN 0 80 :::3307 :::* users:(("mysqld",pid=11365,fd=20))
[root@host57 ~]# ps -C mysqld
PID TTY TIME CMD
11365 pts/0 00:00:00 mysqld
[root@host57 ~]# ls /dir1 #查看数据库目录文件列表
auto.cnf ib_logfile0 mysql mysqld1.sock sys
ib_buffer_pool ib_logfile1 mysqld1.error mysqld1.sock.lock
ibdata1 ibtmp1 mysqld1.pid performance_schema
[root@host57 ~]# mysql -uroot -p',hi=N2b,8/qI' -S /dir1/mysqld1.sock
mysql> alter user root@"localhost" identified by "123456"; #修改新密码
[root@host57 ~]# mysql -uroot -p123456 -S /dir1/mysqld1.sock
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
[root@host57 ~]# ls /dir2
[root@host57 ~]# mysqld_multi start 2 #启动实例2
[root@host57 ~]# ls /dir2 #查看数据库目录文件列表
auto.cnf ib_logfile0 mysql mysqld2.sock.lock
ib_buffer_pool ib_logfile1 mysqld2.error performance_schema
ibdata1 ibtmp1 mysqld2.sock sys
[root@host57 ~]# ss -nutlp | grep 3308
tcp LISTEN 0 80 :::3308 :::* users:(("mysqld",pid=11734,fd=20))
[root@host57 ~]# ps -C mysqld
PID TTY TIME CMD
11365 pts/0 00:00:01 mysqld
11734 pts/0 00:00:00 mysqld
[root@host57 ~]# mysql -uroot -p"dsvL3O<Nzo4%" -S /dir2/mysqld2.sock
mysql> alter user root@"localhost" identified by "123456"; #修改密码
Query OK, 0 rows affected (0.03 sec)
[root@host57 ~]# mysql -uroot -p123456 -S /dir2/mysqld2.sock #新密码登录
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
##############
如果启动服务而且有端口号但无法进入数据库,可以进行如下操作:
rm -rf /dir1/*
killall -9 mysqld #如果没有killall命令,请yum -y install psmisc
检查配置文件/etc/my.cnf
再次重新启动服务mysqld_multi start 1
################
停止服务(如果停止不了服务就杀掉进程)
[root@host57 ~]# ss -nutlp | grep mysqld
tcp LISTEN 0 80 :::3307 :::* users:((mysqld",pid=11365,fd=20))
tcp LISTEN 0 80 :::3308 :::* users:((mysqld",pid=11734,fd=20))
[root@host57 ~]# mysqld_multi --user=root --password=123456 stop 1 #关闭实例1的服务
[root@host57 ~]# ss -nutlp | grep mysqld
tcp LISTEN 0 80 :::3308 :::* users:((mysqld",pid=11734,fd=20))
[root@host57 ~]# mysqld_multi start 1 #再次启动
[root@host57 ~]# mysql -uroot -p123456 -S /dir1/mysqld1.sock #此时再次启动时直接用修改后的密码登录即可
[root@host57 ~]# ss -nutlp | grep mysqld
tcp LISTEN 0 80 :::3307 :::* users:((mysqld",pid=11945,fd=28))
tcp LISTEN 0 80 :::3308 :::* users:((mysqld",pid=11734,fd=20))
添加实例3.自定义运行参数
[root@host57 ~]# vim /etc/my.cnf
[mysqld_multi]
mysqld=/usr/local/mysql/bin/mysqld_safe
mysqladmin=/usr/local/mysql/bin/mysqladmin
user=root
[mysqld1]
datadir=/dir1
log-error=/dir1/mysqld1.error
socket=/dir1/mysqld1.sock
pid-file=/dir1/mysqld1.pid
port=3307
[mysqld2]
datadir=/dir2
log-error=/dir2/mysqld2.error
socket=/dir2/mysqld2.sock
pid-file=/dir2/mysqld2.pid
port=3308
[mysqld3]
datadir=/dir3
log-error=/dir3/mysqld3.error
socket=/dir3/mysqld3.sock
pid-file=/dir3/mysqld3.pid
port=3309
[root@host57 ~]#mkdir /dir3
[root@host57 ~]# ls /dir3
[root@host57 ~]# mysqld_multi start 3
[root@host57 ~]# ls /dir3
auto.cnf ib_logfile0 mysql mysqld3.sock sys
ib_buffer_pool ib_logfile1 mysqld3.error mysqld3.sock.lock
ibdata1 ibtmp1 mysqld3.pid performance_schema
[root@host57 ~]# ss -nutlp | grep mysqld
tcp LISTEN 0 80 :::3307 :::* users:((mysqld",pid=11945,fd=28))
tcp LISTEN 0 80 :::3308 :::* users:((mysqld",pid=11734,fd=20))
tcp LISTEN 0 80 :::3309 :::* users:((mysqld",pid=12140,fd=20))
[root@host57 ~]# mysql -uroot -p'Iorf=e<ef6aM' -S /dir3/mysqld3.sock
mysql> alter user root@"localhost" identified by "123456";
[root@host57 ~]# mysql -uroot -p123456 -S /dir3/mysqld3.sock
允许客户端50可以连接57主机的实例1服务 ,对db1库有完全权限,且实例1可以记录用户执行的SQL命令
[root@host57 ~]# vim /etc/my.cnf
[mysqld1]
server_id=1
log_bin=mysqld1
datadir=/dir1
log-error=/dir1/mysqld1.error
socket=/dir1/mysqld1.sock
pid-file=/dir1/mysqld1.pid
port=3307
[root@host57 ~]# mysqld_multi --user=root --password=123456 stop 1
[root@host57 ~]# mysqld_multi start 1
[root@host57 ~]# mysql -uroot -p123456 -S /dir1/mysqld1.sock
mysql> show master status;
+----------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------+----------+--------------+------------------+-------------------+
| mysqld1.000001 | 154 | | | |
+----------------+----------+--------------+------------------+-------------------+
1 row in set (0.04 sec)
mysql> grant all on db1.* to yaya@"192.168.4.50" identified by "123456";
Query OK, 0 rows affected, 1 warning (0.07 sec)
mysql> select user ,host from mysql.user;
+---------------+--------------+
| user | host |
+---------------+--------------+
| yaya | 192.168.4.50 |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+---------------+--------------+
4 rows in set (0.00 sec)
mysql> show grants for yaya@"192.168.4.50" ;
+----------------------------------------------------------+
| Grants for yaya@192.168.4.50 |
+----------------------------------------------------------+
| GRANT USAGE ON *.* TO 'yaya'@'192.168.4.50' |
| GRANT ALL PRIVILEGES ON `db1`.* TO 'yaya'@'192.168.4.50' |
+----------------------------------------------------------+
2 rows in set (0.00 sec)
[root@host50 ~]# mysql -h192.168.4.57 -uyaya -p123456 -P3307
MySQL [(none)]> show grants;
+----------------------------------------------------------+
| Grants for yaya@192.168.4.50 |
+----------------------------------------------------------+
| GRANT USAGE ON *.* TO 'yaya'@'192.168.4.50' |
| GRANT ALL PRIVILEGES ON `db1`.* TO 'yaya'@'192.168.4.50' |
+----------------------------------------------------------+
2 rows in set (0.00 sec)
MySQL [(none)]> create database db1;
Query OK, 1 row affected (0.02 sec)
MySQL [(none)]> create table db1.user(name char(10));
Query OK, 0 rows affected (0.02 sec)
MySQL [(none)]> insert into db1.user values("aaa");
Query OK, 1 row affected (0.04 sec)
MySQL [(none)]> select * from db1.user;
+------+
| name |
+------+
| aaa |
+------+
1 row in set (0.00 sec)
在host57主机查看
[root@host57 ~]# mysql -uroot -p123456 -S /dir1/mysqld1.sock
mysql> select * from db1.user;
+------+
| name |
+------+
| aaa |
+------+
1 row in set (0.00 sec)