/
搜索到
21
篇与
的结果
-
MySQL 5.7.44 二进制包安装 一、MySQL 5.7.44 二进制包安装可用于部署泛微Ecology9.0OS: Rocky Linux 9.1Database: mysql-5.7.44-linux-glibc2.12-x86_64.tar.gzQQ: 22193771351.0 解压缩tar zxf mysql-5.7.44-linux-glibc2.12-x86_64.tar.gz1.1 创建软链接ln -sf /usr/local/mysql-5.7.44-linux-glibc2.12-x86_64/ /usr/localmysql1.2 创建MySQL用户组groupadd mysql useradd -r -g mysql -s /sbin/nologin mysql1.3 创建数据目录、日志目录，配置文件目录mkdir /usr/local/mysql/data mkdir /usr/local/mysql/etc mkdir /usr/local/mysql/log chown -R mysql:mysql /usr/local/mysql/1.4 编辑MySQL配置文件[client] port=3306 socket=/tmp/mysql.sock [mysqld] port=3306 user = mysql socket=/tmp/mysql.sock tmpdir = /tmp key_buffer_size=256M max_allowed_packet=128M open_files_limit = 60000 explicit_defaults_for_timestamp server-id = 1 character-set-server = utf8 federated max_connections = 1000 group_concat_max_len = 102400 max_connect_errors = 100000 table_open_cache = 4096 event_scheduler = ON skip-name-resolve lower_case_table_names = 1 interactive_timeout = 86400 wait_timeout = 86400 sync_binlog=0 back_log=100 default-storage-engine = InnoDB log_slave_updates = 1 binlog_cache_size = 32M max_heap_table_size = 256M read_rnd_buffer_size = 64M sort_buffer_size = 256M join_buffer_size = 512M thread_cache_size = 300 log_bin_trust_function_creators = 1 read_buffer_size = 32M read_rnd_buffer_size = 128M bulk_insert_buffer_size = 512M sql_mode='NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE,NO_AUTO_CREATE_USER,ERROR_FOR_DIVISION_BY_ZERO' transaction_isolation = READ-COMMITTED tmp_table_size = 512M log-bin=mysql-bin binlog_format=mixed expire_logs_days = 15 slow_query_log = 1 slow_query_log_file = /usr/local/mysql/log/slow.log long_query_time = 5 innodb_buffer_pool_size = 512M innodb_thread_concurrency = 16 innodb_flush_log_at_trx_commit = 2 innodb_log_buffer_size = 32M innodb_log_file_size = 1024M innodb_log_files_in_group = 4 innodb_max_dirty_pages_pct = 90 innodb_lock_wait_timeout = 120 [mysqldump] quick max_allowed_packet = 64M [client] # The following password will be sent to all standard MySQL clients password="my password" [mysqladmin] force [mysql] no-auto-rehash [myisamchk] key_buffer = 16M sort_buffer_size = 16M read_buffer = 8M write_buffer = 8M [mysqlhotcopy] interactive-timeout [mysqld_safe] open-files-limit = 65535 log-error=/usr/local/mysql/log/mysqld.log pid-file=/usr/local/mysql/etc/mysqld.pid1.5 初始化数据库cd /usr/local/mysql ./bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data输出结果：......... 2024-01-15T11:16:44.157258Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started. 2024-01-15T11:16:50.486157Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended. 2024-01-15T11:16:55.164521Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: qyNLr69t%fo* #临时密码1.6 配置环境变量vim /etc/profile.d/mysql.sh export PATH=$PATH:/usr/local/mysql/bin source /etc/profile.d/mysql.sh1.7 配置开机启动 使用systemd管理mysql服务[root@dxm system]# vim /usr/lib/systemd/system/mysqld.service [Unit] Description=MySQL Server After=network.service [Service] User=mysql Group=mysql Type=forking TimeoutSec=0 PermissionsStartOnly=true ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/usr/local/mysql/etc/my.cnf --user=mysql --daemonize ExecStop=/usr/local/mysql/bin/mysql stop LimitNOFILE = 65535 Restart=on-failure RestartSec=10 RestartPreventExitStatus=1 PrivateTmp=false [Install] WantedBy=multi-user.targetsystemctl daemon-reload systemctl enable mysqld systemctl start mysqld systemctl status mysqld1.8 登录MySQL 使用临时密码mysql -uroot -pqyNLr69t%fo*1.9 登录MySQL报错：mysql: error while loading shared libraries: libncurses.so.5: cannot open shared object file: No such file or directory mysql: error while loading shared libraries: libtinfo.so.5: cannot open shared object file: No such file or directory # 解决方法： find / -name 'libncurses*' cd /usr/lib64 ln -sf libncurses.so.6.2 libncurses.so.5 ln -sf libtinfo.so.6.2 libtinfo.so.51.10 修改密码#修改密码 ALTER USER 'root'@'localhost' IDENTIFIED BY '123456' PASSWORD EXPIRE NEVER; #NAVER永不过期 #刷新授权表 flush privileges;1.10 创建ecology数据库CREATE DATABASE ecology; CREATE USER ecology IDENTIFIED WITH mysql_native_password BY 'ecology123'; GRANT ALL PRIVILEGES ON ecology.* to 'ecology'@'%'; FLUSH PRIVILEGES; -
PostgreSQL 16.1 编译安装 一、PostgreSQL 16.1 编译安装可用于部署泛微Ecology9OS: RockyLinux 9.1DB: PostgreSQL 16.1QQ: 22193771351. 关闭默认防火墙 firewallsystemctl stop firewalld.service #停止firewall systemctl disable firewalld.service #禁止firewall开机启动 systemctl mask firewalld systemctl stop firewalld dnf remove firewalld2.安装iptables防火墙dnf install -y iptables-services3.编辑iptables配置文件vim /etc/sysconfig/iptables # sample configuration for iptables service # you can edit this manually or use system-config-firewall # please do not ask us to add additional ports/services to this default configuration *filter :INPUT ACCEPT [0:0] :FORWARD ACCEPT [0:0] :OUTPUT ACCEPT [0:0] -A INPUT -m state --state RELATED,ESTABLISHED -j ACCEPT -A INPUT -p icmp -j ACCEPT -A INPUT -i lo -j ACCEPT -A INPUT -p tcp -m state --state NEW -m tcp --dport 22 -j ACCEPT -A INPUT -p tcp -m state --state NEW -m tcp --dport 5432 -j ACCEPT -A INPUT -j REJECT --reject-with icmp-host-prohibited -A FORWARD -j REJECT --reject-with icmp-host-prohibited COMMIT #最后重启防火墙使配置生效 systemctl restart iptables.service #设置防火墙开机启动 systemctl enable iptables.service #重启防火墙 /usr/libexec/iptables/iptables.init restart 4.关闭SELINUXvim /etc/selinux/config SELINUX=disabled # 使配置生效 setenforce 05.安装依赖包dnf install -y uuid uuid-devel libuuid libuuid-devel dnf install -y tcl tcl-devel perl-ExtUtils-Embed readline-devel zlib-devel pam-devel libxml2-devel libxslt-devel openldap-devel python-devel gcc-c++ openssl-devel cmake gcc* readline-devel icu libicu-devel6. 编译PostgreSQL# 创建目录 mkdir -p /usr/local/pgsql mkdir -p /usr/local/pgsql/data mkdir -p /usr/local/pgsql/log #解压postgreSQL 16.1 tar zxf postgresql-16.1.tar.gz #编译 cd postgresql-16.1 ./configure --prefix=/usr/local/pgsql --with-openssl --with-pgport=5432 --with-tcl --with-perl --with-python --with-libxml --with-libxslt --with-ossp-uuid --with-pam --with-ldap #gmake 含第三方插件全部编译 gmake world #gmake 含第三方插件全部安装 gmake install-world 7.创建用户组groupadd postgres useradd -g postgres postgres8.配置权限chown -R postgres:postgres /usr/local/pgsql chown -R postgres:postgres /usr/local/pgsql/data chown -R postgres:postgres /usr/local/pgsql/log chmod -R 700 /usr/local/pgsql/log9.配置环境变量vim /etc/profile.d/pgsql.sh export PGHOME=/usr/local/pgsql export PGDATA=/usr/local/pgsql/data export PATH=$PATH:$PGHOME/bin export LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH source /etc/profile.d/pgsql.sh10.初始化数据库# 切换到postgres用户 su - postgres #初始化数据库 /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data --encoding=UTF8 --lc-collate=en_US.UTF-8 --lc-ctype=en_US.UTF-811.启动数据库#启动 /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start #停止 /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile stop #重启 /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile restart #查看状态 /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile status 12.配置init启动#切换到root su - root #复制启动文件 cp /usr/local/src/postgresql-14.3/contrib/start-scripts/linux /etc/init.d/postgresql #设置运行权限 chmod +x /etc/init.d/postgresql #编辑修改 vi /etc/init.d/postgresql prefix=/usr/local/pgsql PGDATA="/usr/local/pgsql/data" PGUSER=postgres service postgresql start service postgresql restart service postgresql stop chkconfig postgresql on13.配置systemd启动vi /usr/lib/systemd/system/postgresql.service [Unit] Description=PostgreSQL Database Server After=syslog.target After=network.target [Service] Type=forking User=postgres Group=postgres ExecStart=/usr/local/pgsql/bin/pg_ctl start -D /usr/local/pgsql/data ExecStop=/usr/local/pgsql/bin/pg_ctl stop ExecReload=/usr/local/pgsql/bin/pg_ctl reload -D /usr/local/pgsql/data TimeoutSec=300 [Install] WantedBy=multi-user.targetsystemctl daemon-reload systemctl start postgresql systemctl enable postgresql systemctl restart postgresql systemctl status postgresql14.配置密码#切换到postgres用户 su - postgres #连接数据库 psql -U postgres -d postgres -hlocalhost -p5432 #参数：-U指定用户，-d指定数据库，-h指定服务器，-p指定端口 # 修改数据库用户postgres的密码为postgres ALTER USER postgres WITH PASSWORD 'postgres' #退出控制台 postgres=#\q 15.修改配置参数vi /usr/local/pgsql/data/postgresql.conf port = 5432 max_connections = 1000 listen_addresses = '*' log_destination = 'csvlog' logging_collector = on log_directory = 'log' log_filename = 'postgresql-%d.log' log_file_mode = 0600 log_rotation_age = 1d log_rotation_size = 1024MB log_truncate_on_rotation = on log_lock_waits = off log_statement = 'none' log_duration = on log_min_duration_statement = 0 log_connections = off log_disconnections = off log_line_prefix = '%m [%p] %u %d %r' log_timezone = 'Asia/Shanghai' 16.配置数据库访问权限vi /usr/local/pgsql/data/pg_hba.conf # TYPE DATABASE USER ADDRESS METHOD # IPv4 local connections: host all all 127.0.0.1/32 trust host all all 0.0.0.0/0 md5 systemctl restart postgresql17. 创建数据库CREATE USER ecology WITH PASSWORD 'ecology123'; CREATE DATABASE ecology OWNER ecology; GRANT ALL PRIVILEGES ON DATABASE ecology to ecology; GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO ecology;
-
MySQL - SHOW语句 SHOW 语句MySQL [(none)]> HELP SHOW;一. SHOW {BINARY | MASTER} LOGS 列出二进制列表MySQL [(none)]> SHOW BINARY LOGS\G *************************** 1. row *************************** Log_name: mysql-bin.000015 File_size: 4714627 2 rows in set (0.00 sec) MySQL [(none)]> SHOW MASTER LOGS\G *************************** 1. row *************************** Log_name: mysql-bin.000015 File_size: 4714627 2 rows in set (0.00 sec)二. SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count] 列出二进制日志事件IN 'log_name' 指定要查询的binlog文件名(不指定就是第一个binlog文件)FROM pos 指定从哪个pos起始点开始查起(不指定就是从整个文件首个pos点开始算)LIMIT [offset,] 偏移量(不指定就是0)row_count 查询总条数(不指定就是所有行)MySQL [(none)]> SHOW BINLOG EVENTS IN 'mysql-bin.000015' LIMIT 3,2\G *************************** 1. row *************************** Log_name: mysql-bin.000015 Pos: 219 Event_type: Query Server_id: 1 End_log_pos: 300 Info: BEGIN *************************** 2. row *************************** Log_name: mysql-bin.000015 Pos: 300 Event_type: Query Server_id: 1 End_log_pos: 526rows in set (0.01 sec) SHOW CHARACTER SET [like_or_where] SHOW COLLATION [like_or_where] SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [like_or_where] SHOW CREATE DATABASE db_name SHOW CREATE EVENT event_name SHOW CREATE FUNCTION func_name SHOW CREATE PROCEDURE proc_name SHOW CREATE TABLE tbl_name SHOW CREATE TRIGGER trigger_name SHOW CREATE VIEW view_name SHOW DATABASES [like_or_where] SHOW ENGINE engine_name {STATUS | MUTEX} SHOW [STORAGE] ENGINES SHOW ERRORS [LIMIT [offset,] row_count] SHOW EVENTS SHOW FUNCTION CODE func_name SHOW FUNCTION STATUS [like_or_where] SHOW GRANTS FOR user SHOW INDEX FROM tbl_name [FROM db_name] SHOW MASTER STATUS SHOW OPEN TABLES [FROM db_name] [like_or_where] SHOW PLUGINS SHOW PROCEDURE CODE proc_name SHOW PROCEDURE STATUS [like_or_where] SHOW PRIVILEGES SHOW [FULL] PROCESSLIST SHOW PROFILE [types] [FOR QUERY n] [OFFSET n] [LIMIT n] SHOW PROFILES SHOW RELAYLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count] SHOW SLAVE HOSTS SHOW SLAVE STATUS [FOR CHANNEL channel] SHOW [GLOBAL | SESSION] STATUS [like_or_where] SHOW TABLE STATUS [FROM db_name] [like_or_where] SHOW [FULL] TABLES [FROM db_name] [like_or_where] SHOW TRIGGERS [FROM db_name] [like_or_where] SHOW [GLOBAL | SESSION] VARIABLES [like_or_where] SHOW WARNINGS [LIMIT [offset,] row_count] like_or_where: { LIKE 'pattern' | WHERE expr } If the syntax for a given SHOW statement includes a LIKE 'pattern' part, 'pattern' is a string that can contain the SQL % and _ wildcard characters. The pattern is useful for restricting statement output to matching values. Several SHOW statements also accept a WHERE clause that provides more flexibility in specifying which rows to display. See https://dev.mysql.com/doc/refman/5.7/en/extended-show.html. URL: https://dev.mysql.com/doc/refman/5.7/en/show.html
-
MySQL 8.0.15 - AND 、OR、NOT 运算符 MySQL AND 、OR、NOT 运算符例1 AND运算符 查询出生日期在1990年以后，并且 积分大于1000SELECT * FROM customers WHERE birth_date > '1990-01-01' AND points > 1000注：AND 运算符 两个条件都要符合才会返回结果集。输出 例2 OR运算符 查询出生日期在1990年以后，并且 积分大于1000SELECT * FROM customers WHERE birth_date > '1990-01-01' OR points > 1000注：OR 运算符 只要有一个条件符合就会返回结果集。输出 例3 NOT运算符 查询出生日期在1990年以后，并且 积分大于1000SELECT * FROM customers WHERE NOT(birth_date > '1990-01-01' OR points > 1000);注：NOT 运算符 对结果集进行非运算。输出 练习 获取订单id 6的，并且总价>30SELECT * FROM order_items WHERE order_id = 6 AND unit_price * quantity > 30;输出
-
