1.查看PROXYSQL的数据库
[root@mysql1 proxysql]# mysql -uadmin -padmin -P6032 -h127.0.0.1
Logging to file '/data/mysql/logs/query.log'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.30 (ProxySQL Admin Module)
Copyright (c) 2000, 2021, 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.
"admin@127.0.0.1">show databases;
+-----+---------------+-------------------------------------+
| seq | name | file |
+-----+---------------+-------------------------------------+
| 0 | main | |
| 2 | disk | /var/lib/proxysql/proxysql.db |
| 3 | stats | |
| 4 | monitor | |
| 5 | stats_history | /var/lib/proxysql/proxysql_stats.db |
+-----+---------------+-------------------------------------+
5 rows in set (0.00 sec)
"admin@127.0.0.1">
2.查看proxysql的变量
"admin@127.0.0.1">show variables;
+-----------------------------------------------------+--------------------+
| Variable_name | Value |
+-----------------------------------------------------+--------------------+
| admin-admin_credentials | admin:admin |
| admin-checksum_mysql_query_rules | true |
| admin-checksum_mysql_servers | true |
| admin-checksum_mysql_users | true |
| admin-cluster_check_interval_ms | 1000 |
| admin-cluster_check_status_frequency | 10 |
| admin-cluster_mysql_query_rules_diffs_before_sync | 3 |
| admin-cluster_mysql_query_rules_save_to_disk | true |
| admin-cluster_mysql_servers_diffs_before_sync | 3 |
| admin-cluster_mysql_servers_save_to_disk | true |
| admin-cluster_mysql_users_diffs_before_sync | 3 |
| admin-cluster_mysql_users_save_to_disk | true |
| admin-cluster_password | |
| admin-cluster_proxysql_servers_diffs_before_sync | 3 |
| admin-cluster_proxysql_servers_save_to_disk | true |
| admin-cluster_username | |
| admin-hash_passwords | true |
| admin-mysql_ifaces | 0.0.0.0:6032 |
| admin-read_only | false |
| admin-refresh_interval | 2000 |
| admin-stats_credentials | stats:stats |
| admin-stats_mysql_connection_pool | 60 |
| admin-stats_mysql_connections | 60 |
| admin-stats_mysql_query_cache | 60 |
| admin-stats_system_cpu | 60 |
| admin-stats_system_memory | 60 |
| admin-telnet_admin_ifaces | (null) |
| admin-telnet_stats_ifaces | (null) |
| admin-version | 1.4.9-3-gd9fd599 |
| admin-web_enabled | false |
| admin-web_port | 6080 |
| mysql-autocommit_false_is_transaction | false |
| mysql-autocommit_false_not_reusable | false |
| mysql-client_found_rows | true |
| mysql-client_multi_statements | true |
| mysql-commands_stats | true |
| mysql-connect_retries_delay | 1 |
| mysql-connect_retries_on_failure | 10 |
| mysql-connect_timeout_server | 3000 |
| mysql-connect_timeout_server_max | 10000 |
| mysql-connection_delay_multiplex_ms | 0 |
| mysql-connection_max_age_ms | 0 |
| mysql-connpoll_reset_queue_length | 50 |
| mysql-default_charset | utf8 |
| mysql-default_max_latency_ms | 1000 |
| mysql-default_query_delay | 0 |
| mysql-default_query_timeout | 36000000 |
| mysql-default_reconnect | true |
| mysql-default_schema | information_schema |
| mysql-default_sql_mode | |
| mysql-default_time_zone | SYSTEM |
| mysql-enforce_autocommit_on_reads | false |
| mysql-eventslog_filename | |
| mysql-eventslog_filesize | 104857600 |
| mysql-forward_autocommit | false |
| mysql-free_connections_pct | 10 |
| mysql-have_compress | true |
| mysql-hostgroup_manager_verbose | 1 |
| mysql-init_connect | |
| mysql-interfaces | 0.0.0.0:6033 |
| mysql-long_query_time | 1000 |
| mysql-max_allowed_packet | 4194304 |
| mysql-max_connections | 2048 |
| mysql-max_stmts_cache | 10000 |
| mysql-max_stmts_per_connection | 20 |
| mysql-max_transaction_time | 14400000 |
| mysql-mirror_max_concurrency | 16 |
| mysql-mirror_max_queue_length | 32000 |
| mysql-monitor_connect_interval | 60000 |
| mysql-monitor_connect_timeout | 600 |
| mysql-monitor_enabled | true |
| mysql-monitor_groupreplication_healthcheck_interval | 5000 |
| mysql-monitor_groupreplication_healthcheck_timeout | 800 |
| mysql-monitor_history | 600000 |
| mysql-monitor_password | monitor |
| mysql-monitor_ping_interval | 10000 |
| mysql-monitor_ping_max_failures | 3 |
| mysql-monitor_ping_timeout | 1000 |
| mysql-monitor_query_interval | 60000 |
| mysql-monitor_query_timeout | 100 |
| mysql-monitor_read_only_interval | 1500 |
| mysql-monitor_read_only_max_timeout_count | 3 |
| mysql-monitor_read_only_timeout | 500 |
| mysql-monitor_replication_lag_interval | 10000 |
| mysql-monitor_replication_lag_timeout | 1000 |
| mysql-monitor_replication_lag_use_percona_heartbeat | |
| mysql-monitor_slave_lag_when_null | 60 |
| mysql-monitor_username | monitor |
| mysql-monitor_wait_timeout | true |
| mysql-monitor_writer_is_also_reader | true |
| mysql-multiplexing | true |
| mysql-ping_interval_server_msec | 120000 |
| mysql-ping_timeout_server | 500 |
| mysql-poll_timeout | 2000 |
| mysql-poll_timeout_on_failure | 100 |
| mysql-query_cache_size_MB | 256 |
| mysql-query_digests | true |
| mysql-query_digests_lowercase | false |
| mysql-query_digests_max_digest_length | 2048 |
| mysql-query_digests_max_query_length | 65000 |
| mysql-query_processor_iterations | 0 |
| mysql-query_processor_regex | 1 |
| mysql-query_retries_on_failure | 1 |
| mysql-server_capabilities | 45578 |
| mysql-server_version | 5.5.30 |
| mysql-servers_stats | true |
| mysql-session_idle_ms | 1000 |
| mysql-session_idle_show_processlist | true |
| mysql-sessions_sort | true |
| mysql-shun_on_failures | 5 |
| mysql-shun_recovery_time_sec | 10 |
| mysql-ssl_p2s_ca | |
| mysql-ssl_p2s_cert | |
| mysql-ssl_p2s_cipher | |
| mysql-ssl_p2s_key | |
| mysql-stacksize | 1048576 |
| mysql-stats_time_backend_query | false |
| mysql-stats_time_query_processor | false |
| mysql-threads | 4 |
| mysql-threshold_query_length | 524288 |
| mysql-threshold_resultset_size | 4194304 |
| mysql-throttle_connections_per_sec_to_hostgroup | 1000000 |
| mysql-throttle_max_bytes_per_second_to_client | 2147483647 |
| mysql-throttle_ratio_server_to_client | 0 |
| mysql-verbose_query_error | false |
| mysql-wait_timeout | 28800000 |
+-----------------------------------------------------+--------------------+
126 rows in set (0.00 sec)
"admin@127.0.0.1">select @@admin-admin_credentials;
+---------------------------+
| @@admin-admin_credentials |
+---------------------------+
| admin:admin |
+---------------------------+
1 row in set (0.00 sec)
3.创建新的管理用户
"admin@127.0.0.1">set admin-admin_credentials='admin:admin;sspuadmin:sspuadmin';
Query OK, 1 row affected (0.00 sec)
"admin@127.0.0.1"> select @@admin-admin_credentials;
+---------------------------------+
| @@admin-admin_credentials |
+---------------------------------+
| admin:admin;sspuadmin:sspuadmin |
+---------------------------------+
1 row in set (0.00 sec)
--同时新增的账号信息加入到运行环境和保存到磁盘。
"admin@127.0.0.1">load admin variables to runtime;
Query OK, 0 rows affected (0.00 sec)
"admin@127.0.0.1">save admin variables to disk;
Query OK, 31 rows affected (0.00 sec)
4.新创建的用户登陆
[root@mysql1 proxysql]# mysql -usspuadmin -psspuadmin -P6032 -h192.168.1.11
Logging to file '/data/mysql/logs/query.log'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.30 (ProxySQL Admin Module)
Copyright (c) 2000, 2021, 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.
"sspuadmin@192.168.1.11">show databases;
+-----+---------------+-------------------------------------+
| seq | name | file |
+-----+---------------+-------------------------------------+
| 0 | main | |
| 2 | disk | /var/lib/proxysql/proxysql.db |
| 3 | stats | |
| 4 | monitor | |
| 5 | stats_history | /var/lib/proxysql/proxysql_stats.db |
+-----+---------------+-------------------------------------+
5 rows in set (0.00 sec)
5.创建监控账号并使用
"sspuadmin@192.168.1.11"> set admin-stats_credentials='stats:stats;sspumonitor:sspumonitor';
Query OK, 1 row affected (0.00 sec)
"sspuadmin@192.168.1.11">select @@admin-stats_credentials;
+-------------------------------------+
| @@admin-stats_credentials |
+-------------------------------------+
| stats:stats;sspumonitor:sspumonitor |
+-------------------------------------+
1 row in set (0.00 sec)
"sspuadmin@192.168.1.11">load admin variables to runtime;
Query OK, 0 rows affected (0.00 sec)
"sspuadmin@192.168.1.11">save admin variables to disk;
Query OK, 31 rows affected (0.00 sec)
6.查看自定义的数据库中都有哪些表。
"sspuadmin@192.168.1.11">show tables from main;
+--------------------------------------------+
| tables |
+--------------------------------------------+
| global_variables |
| mysql_collations |
| mysql_group_replication_hostgroups |
| mysql_query_rules |
| mysql_query_rules_fast_routing |
| mysql_replication_hostgroups |
| mysql_servers |
| mysql_users |
| proxysql_servers |
| runtime_checksums_values |
| runtime_global_variables |
| runtime_mysql_group_replication_hostgroups |
| runtime_mysql_query_rules |
| runtime_mysql_query_rules_fast_routing |
| runtime_mysql_replication_hostgroups |
| runtime_mysql_servers |
| runtime_mysql_users |
| runtime_proxysql_servers |
| runtime_scheduler |
| scheduler |
+--------------------------------------------+
20 rows in set (0.00 sec)
"sspuadmin@192.168.1.11">show tables from disk;
+------------------------------------+
| tables |
+------------------------------------+
| global_variables |
| mysql_collations |
| mysql_group_replication_hostgroups |
| mysql_query_rules |
| mysql_query_rules_fast_routing |
| mysql_replication_hostgroups |
| mysql_servers |
| mysql_users |
| proxysql_servers |
| scheduler |
+------------------------------------+
10 rows in set (0.00 sec)
"sspuadmin@192.168.1.11">show tables from stats;
+--------------------------------------+
| tables |
+--------------------------------------+
| global_variables |
| stats_memory_metrics |
| stats_mysql_commands_counters |
| stats_mysql_connection_pool |
| stats_mysql_connection_pool_reset |
| stats_mysql_global |
| stats_mysql_prepared_statements_info |
| stats_mysql_processlist |
| stats_mysql_query_digest |
| stats_mysql_query_digest_reset |
| stats_mysql_query_rules |
| stats_mysql_users |
| stats_proxysql_servers_checksums |
| stats_proxysql_servers_metrics |
| stats_proxysql_servers_status |
+--------------------------------------+
15 rows in set (0.00 sec)
"sspuadmin@192.168.1.11">show tables from monitor;
+------------------------------------+
| tables |
+------------------------------------+
| mysql_server_connect_log |
| mysql_server_group_replication_log |
| mysql_server_ping_log |
| mysql_server_read_only_log |
| mysql_server_replication_lag_log |
+------------------------------------+
5 rows in set (0.00 sec)
"sspuadmin@192.168.1.11">show tables from stats_history;
+------------------------+
| tables |
+------------------------+
| mysql_connections |
| mysql_connections_day |
| mysql_connections_hour |
| mysql_query_cache |
| mysql_query_cache_day |
| mysql_query_cache_hour |
| system_cpu |
| system_cpu_day |
| system_cpu_hour |
| system_memory |
| system_memory_day |
| system_memory_hour |
+------------------------+
12 rows in set (0.00 sec)