Monthly Archives: September 2018

–创建测试数据 create table Student(S# varchar(10),Sname nvarchar(10),Sage datetime,Ssex nvarchar(10)) insert into Student values(’01’ , N’赵雷’ , ‘1990-01-01′ , N’男’) insert into Student values(’02’ , N’钱电’ , ‘1990-12-21′ , N’男’) insert into Student values(’03’ , N’孙风’ , ‘1990-05-20′ , N’男’) insert into … Continue reading →

+++++++++++++++++++++++++++++++++++++++++++++++++++ 表基本操作练习题 1 复制user表的所有记录到teadb库的teacher表里 只复制user表的表结构给新teadb库的tea2表 mysql> create table teacher select * from teadb; Query OK, 41 rows affected (0.32 sec) Records: 41  Duplicates: 0  Warnings: 0   mysql> select * from teacher; +—-+———————+——+——+——–+———-+——-+——-+—————————————————————–+—————————+—————-+———-+ | id | name                | sex  | age  | s_year | password | uid   | gid   | comment                                                         | homedir                   | shell          | pay      | +—-+———————+——+——+——–+———-+——-+——-+—————————————————————–+—————————+—————-+———-+ |  1 | root                | boy  |   21 |   1990 | x        |     0 |     0 | root                                                            | /root                     | /sbin/nologin  | 30000.00 | |  2 | bin                 | boy  |   21 |   1990 | x        |     1 |     1 | bin                                                             | /bin                      | /sbin/nologin  |  5000.00 | |  4 | adm                 | boy  |   21 |   1990 | x        |     3 |     4 | adm                                                             | /var/adm                  | /sbin/nologin  |  5000.00 | |  5 | lp                  | boy  |   21 |   1990 | x        |     4 |     7 | lp                                                              | /var/spool/lpd            | /sbin/nologin  |  5000.00 | |  6 | sync                | boy  |   21 |   1990 | x        |     5 |     0 | sync                                                            | /sbin                     | /sbin/nologin  |  5000.00 | |  7 | shutdown            | boy  |   21 |   1990 | x        |     6 |     0 | shutdown                                                        | /sbin                     | /sbin/shutdown |  5000.00 | |  8 | halt                | boy  |   21 |   1990 | x        |     7 |     0 | halt                                                            | /sbin                     | /sbin/halt     |  5000.00 | |  9 | mail                | boy  |   21 |   1990 | x        |     8 |    12 | mail                                                            | /var/spool/mail           | /sbin/nologin  |  5000.00 | | 10 | operator            | girl |   21 |   1990 | x        |    11 |     0 | operator                                                        | /root                     | /sbin/nologin  | 10000.00 | | 11 | games               | girl |   21 |   1990 | x        |    12 |   100 | games                                                           | /root                     | /sbin/nologin  | 10000.00 | | 12 | ftp                 | girl |   21 |   1990 | x        |    14 |    50 | FTP User                                                        | /var/ftp                  | /sbin/nologin  | 10000.00 | | 13 | nobody              | girl |   21 |   1990 | x        |    99 |    99 | Nobody                                                          | /                         | /sbin/nologin  | 10000.00 | | 14 | systemd-network     | girl |   21 |   1990 | x        |   192 |   192 | systemd Network Management                                      | /root                     | /sbin/nologin  | 10000.00 | | 16 | polkitd             | girl |   21 |   1990 | x        |   999 |   998 | User for polkitd                                                | /                         | /sbin/nologin  | 10000.00 | | 17 | libstoragemgmt      | girl |   21 |   1990 | x        |   998 |   996 | daemon account for libstoragemgmt                               | /var/run/lsm              | /sbin/nologin  | 10000.00 | | 18 | rpc                 | girl |   21 |   1990 | x        |    32 |    32 | Rpcbind Daemon                                                  | /var/lib/rpcbind          | /sbin/nologin  | 10000.00 | | 19 | colord              | girl |   21 |   1990 | x        |   997 |   995 | User for colord                                                 | /var/lib/colord           | /sbin/nologin  | 10000.00 | | 20 | saslauth            | girl |   21 |   1990 | x        |   996 |    76 | Saslauthd user                                                  | /run/saslauthd            | /sbin/nologin  | 10000.00 | | 21 | abrt                | girl |   21 |   1990 | x        |   173 |   173 |                                                                 | /root                     | /sbin/nologin  | 10000.00 | | 22 | rtkit               | girl |   21 |   1990 | x        |   172 |   172 | RealtimeKit                                                     | /root                     | /sbin/nologin  | 10000.00 | | 23 | radvd               | girl |   21 |   1990 | x        |    75 |    75 | radvd user                                                      | /                         | /sbin/nologin  | 10000.00 | | 24 | chrony              | girl |   21 |   1990 | x        |   995 |   993 |                                                                 | /var/lib/chrony           | /sbin/nologin  | 10000.00 | | 25 | tss                 | girl |   21 |   1990 | x        |    59 |    59 | Account used by the trousers package to sandbox the tcsd daemon | /dev/null                 | /sbin/nologin  | 10000.00 | | 26 | usbmuxd             | girl |   21 |   1990 | x        |   113 |   113 | usbmuxd user                                                    | /root                     | /sbin/nologin  | 10000.00 | | 27 | geoclue             | girl |   21 |   1990 | x        |   994 |   991 | User for geoclue                                                | /var/lib/geoclue          | /sbin/nologin  | 10000.00 | | 28 | qemu                | girl |   21 |   1990 | x        |   107 |   107 | qemu user                                                       | /root                     | /sbin/nologin  | 10000.00 | | 29 | rpcuser             | girl |   21 |   1990 | x        |    29 |    29 | RPC Service User                                                | /var/lib/nfs              | /sbin/nologin  | 10000.00 | | 30 | nfsnobody           | girl |   21 |   1990 | x        | 65534 | 65534 | Anonymous NFS User                                              | /var/lib/nfs              | /sbin/nologin  | 10000.00 | … Continue reading →

物理备份 rm -rf /var/lib/mysql/* systemctl stop mysqld rm -rf /var/lib/mysql 把刚才的备份文件拷贝到的当前库下 scp 192.168.4.50:/root/mysql.bak /var/lib/mysql/ 重启正常 重新初始化数据库 1、停数据库服务 systemctl stop mysqld 2、删除库 rm -rf /var/lib/mysql/* 3、重新启动，mysql数据库会自己检测 [root@51 mysql]# grep password /var/log/mysqld.log | tail 2018-09-13T02:33:34.691299Z 0 [Note] Shutting down plugin ‘sha256_password’ 2018-09-13T02:33:34.691305Z … Continue reading →

复制表 1、把整张表，包括数据，全部复制 mysql> create table tea8 select * from tea7; Query OK, 8 rows affected (0.32 sec) Records: 8 Duplicates: 0 Warnings: 0 mysql> select * from tea8; +—-+—–+——–+———+————-+ | id | age | name | sex | hobby | … Continue reading →

mysql> show engines; +——————–+———+—————————————————————-+————–+——+————+ | Engine | Support | Comment | Transactions | XA | Savepoints | +——————–+———+—————————————————————-+————–+——+————+ | InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES | | MRG_MYISAM | … Continue reading →

1，创建2个表，练习约束条件 mysql> create database test; Query OK, 1 row affected (0.00 sec) mysql> use test; Database changed mysql> create table tea(name varchar(4) not null,gender enum(‘boy’,’girl’) default “boy”,interest set(‘book’,’film’,’music’,’football’)); Query OK, 0 rows affected (0.36 sec) mysql> desc tea; +———-+—————————————+——+—–+———+——-+ | … Continue reading →

[root@50 mysql]# grep password /var/log/mysqld.log 2018-09-06T03:35:28.791445Z 1 [Note] A temporary password is generated for root@localhost: <:6hlKMNfoUM

Mycat 分库分表 IP规划 client 192.168.4.254 Mycat 192.168.4.56 c1 192.168.4.55 c2 192.168.4.54 [root@56 ~]# tar -xf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz [root@56 ~]# ll 总用量 985192 -rw——-. 1 root root 1838 1月 30 2018 anaconda-ks.cfg -rw-r–r–. 1 root root 169714648 9月 17 17:10 databases2.sql -rw-r–r–. … Continue reading →

mysql> select * from user; +—-+———————+——+——+——+—————————————————————–+—————————+—————-+——+———-+———-+ | id | username | pass | uid | gid | comment | homedir | shell | sex | homedir2 | pay | +—-+———————+——+——+——+—————————————————————–+—————————+—————-+——+———-+———-+ | 1 | root | x | 0 | 0 … Continue reading →

物理备份 rm -rf /var/lib/mysql/* systemctl stop mysqld rm -rf /var/lib/mysql 把刚才的备份文件拷贝到的当前库下 scp 192.168.4.50:/root/mysql.bak /var/lib/mysql/ 重启正常 重新初始化数据库 1、停数据库服务 systemctl stop mysqld 2、删除库 rm -rf /var/lib/mysql/* 3、重新启动，mysql数据库会自己检测 [root@51 mysql]# grep password /var/log/mysqld.log | tail 2018-09-13T02:33:34.691299Z 0 [Note] Shutting down plugin ‘sha256_password’ 2018-09-13T02:33:34.691305Z … Continue reading →