반응형
'/var/run/mysqld/mysqld 소켓을 통해 로컬 MySQL 서버에 연결할 수 없습니다.sock'(111 "Connection rejected"),
Debian Wheezy 7.5에 mariadb galera 클러스터를 설정하려고 합니다.여러 가지 지시사항을 찾았지만, 모두 조금씩 달랐지만, 지금까지 효과가 있었던 것은 하나도 없습니다.
2노드 클러스터를 설정하려고 합니다.
프라이머리 노드에서는 기본 my.cnf를 사용하고 있으며 conf.d/cluster.cnf에는 다음과 같은 추가 설정이 있습니다.
[mysqld]
#mysql settings
bind-address=10.1.1.139
query_cache_size=0
query_cache_type=0
binlog_format=ROW
default_storage_engine=innodb
innodb_autoinc_lock_mode=2
innodb_doublewrite=1
#galery settings
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://10.1.1.139,10.1.1.140"
wsrep_sst_method=rsync
wsrep_cluster_name="sql_cluster"
wsrep_node_incoming_address=10.1.1.139
wsrep_sst_receive_address=10.1.1.139
wsrep_sst_auth=cluster:password
wsrep_node_address='10.1.1.139'
wsrep_node_name='sql1'
wsrep_on=ON
클러스터 사용자를 생성하여 해당 사용자에게 필요한 모든 권한을 부여하고 서버를 정상적으로 기동했습니다.
service mysql start --wsrep-new-cluster
클러스터가 시작되고cluster_size=1;
두 번째 노드에서는 기본 my.cnf를 사용하고 있으며 conf.d/cluster.cnf에는 다음과 같은 추가 설정이 있습니다.
[mysqld]
#mysql settings
bind-address=10.1.1.140
query_cache_size=0
query_cache_type=0
binlog_format=ROW
default_storage_engine=innodb
innodb_autoinc_lock_mode=2
innodb_doublewrite=1
#galery settings
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://10.1.1.139,10.1.1.140"
wsrep_sst_method=rsync
wsrep_cluster_name="sql_cluster"
wsrep_node_incoming_address=10.1.1.140
wsrep_sst_receive_address=10.1.1.140
wsrep_sst_auth=cluster:password
wsrep_node_address='10.1.1.140'
wsrep_node_name='sql1'
wsrep_on=ON
또한 다음 제안에 따라 세컨더리 노드의 debian.cnf를 프라이머리 노드의 debian.cnf로 교체했습니다.
http://docs.openstack.org/high-availability-guide/content/ha-aa-db-mysql-galera.html에서 적절한 권한을 부여했습니다(다른 장소에서도 제안되었습니다.현재는 링크가 없습니다).
양쪽 노드의 debian.cnf 내용:
[client]
host = localhost
user = debian-sys-maint
password = <password>
socket = /var/run/mysqld/mysqld.sock
[mysql_upgrade]
host = localhost
user = debian-sys-maint
password = <password>
socket = /var/run/mysqld/mysqld.sock
basedir = /usr
두 번째 노드를 시작하려고 할 때:
service mysql start
실패하여 /var/log/syslog에 다음 메시지가 나타납니다.
May 7 19:45:30 ns514282 mysqld_safe: Starting mysqld daemon with databases from /var/lib/mysql
May 7 19:45:30 ns514282 mysqld_safe: WSREP: Running position recovery with --log_error='/var/lib/mysql/wsrep_recovery.s6Uwyc' --pid-file='/var/lib/mysql/ns514282.ip-167-114-159.net-recover.pid'
May 7 19:45:33 ns514282 mysqld_safe: WSREP: Recovered position 00000000-0000-0000-0000-000000000000:-1
May 7 19:45:33 ns514282 mysqld: 150507 19:45:33 [Note] WSREP: wsrep_start_position var submitted: '00000000-0000-0000-0000-000000000000:-1'
May 7 19:45:33 ns514282 mysqld: 150507 19:45:33 [Note] WSREP: Read nil XID from storage engines, skipping position init
May 7 19:45:33 ns514282 mysqld: 150507 19:45:33 [Note] WSREP: wsrep_load(): loading provider library '/usr/lib/galera/libgalera_smm.so'
May 7 19:45:33 ns514282 mysqld: 150507 19:45:33 [Note] WSREP: wsrep_load(): Galera 3.9(rXXXX) by Codership Oy <info@codership.com> loaded successfully.
May 7 19:45:33 ns514282 mysqld: 150507 19:45:33 [Note] WSREP: CRC-32C: using hardware acceleration.
May 7 19:45:33 ns514282 mysqld: 150507 19:45:33 [Note] WSREP: Found saved state: 00000000-0000-0000-0000-000000000000:-1
May 7 19:45:33 ns514282 mysqld: 150507 19:45:33 [Note] WSREP: Passing config to GCS: base_host = 10.1.1.142; base_port = 4567; cert.log_conflicts = no; debug = no; evs.auto_evict = 0; evs.delay_margin = PT1S; evs.delayed_keep_period = PT30S; evs.inactive_check_period = PT0.5S; evs.inactive_timeout = PT15S; evs.join_retrans_period = PT1S; evs.max_install_timeouts = 3; evs.send_window = 4; evs.stats_report_period = PT1M; evs.suspect_timeout = PT5S; evs.user_send_window = 2; evs.view_forget_timeout = PT24H; gcache.dir = /var/lib/mysql/; gcache.keep_pages_size = 0; gcache.mem_size = 0; gcache.name = /var/lib/mysql//galera.cache; gcache.page_size = 128M; gcache.size = 128M; gcs.fc_debug = 0; gcs.fc_factor = 1.0; gcs.fc_limit = 16; gcs.fc_master_slave = no; gcs.max_packet_size = 64500; gcs.max_throttle = 0.25; gcs.recv_q_hard_limit = 9223372036854775807; gcs.recv_q_soft_limit = 0.25; gcs.sync_donor = no; gmcast.segment = 0; gmcast.version = 0; pc.announce_timeout = PT3S; pc.checksum = false; pc.ignore_quorum = false; pc.ignore_sb = false; pc.npv
May 7 19:45:33 ns514282 mysqld: o = false; pc.recovery
May 7 19:45:33 ns514282 mysqld: 150507 19:45:33 [Note] WSREP: Service thread queue flushed.
May 7 19:45:33 ns514282 mysqld: 150507 19:45:33 [Note] WSREP: Assign initial position for certification: -1, protocol version: -1
May 7 19:45:33 ns514282 mysqld: 150507 19:45:33 [Note] WSREP: wsrep_sst_grab()
May 7 19:45:33 ns514282 mysqld: 150507 19:45:33 [Note] WSREP: Start replication
May 7 19:45:33 ns514282 mysqld: 150507 19:45:33 [Note] WSREP: Setting initial position to 00000000-0000-0000-0000-000000000000:-1
May 7 19:45:33 ns514282 mysqld: 150507 19:45:33 [Note] WSREP: protonet asio version 0
May 7 19:45:33 ns514282 mysqld: 150507 19:45:33 [Note] WSREP: Using CRC-32C for message checksums.
May 7 19:45:33 ns514282 mysqld: 150507 19:45:33 [Note] WSREP: backend: asio
May 7 19:45:33 ns514282 mysqld: 150507 19:45:33 [Note] WSREP: restore pc from disk successfully
May 7 19:45:33 ns514282 mysqld: 150507 19:45:33 [Note] WSREP: GMCast version 0
May 7 19:45:33 ns514282 mysqld: 150507 19:45:33 [Note] WSREP: (66b559a2, 'tcp://0.0.0.0:4567') listening at tcp://0.0.0.0:4567
May 7 19:45:33 ns514282 mysqld: 150507 19:45:33 [Note] WSREP: (66b559a2, 'tcp://0.0.0.0:4567') multicast: , ttl: 1
May 7 19:45:33 ns514282 mysqld: 150507 19:45:33 [Note] WSREP: EVS version 0
May 7 19:45:33 ns514282 mysqld: 150507 19:45:33 [Note] WSREP: gcomm: connecting to group 'bfm_cluster', peer '10.1.1.141:,10.1.1.142:'
May 7 19:45:33 ns514282 mysqld: 150507 19:45:33 [Warning] WSREP: (66b559a2, 'tcp://0.0.0.0:4567') address 'tcp://10.1.1.142:4567' points to own listening address, blacklisting
May 7 19:45:33 ns514282 mysqld: 150507 19:45:33 [Note] WSREP: (66b559a2, 'tcp://0.0.0.0:4567') address 'tcp://10.1.1.142:4567' pointing to uuid 66b559a2 is blacklisted, skipping
May 7 19:45:33 ns514282 mysqld: 150507 19:45:33 [Note] WSREP: (66b559a2, 'tcp://0.0.0.0:4567') turning message relay requesting on, nonlive peers:
May 7 19:45:34 ns514282 mysqld: 150507 19:45:34 [Note] WSREP: declaring dc2b490d at tcp://10.1.1.141:4567 stable
May 7 19:45:34 ns514282 mysqld: 150507 19:45:34 [Note] WSREP: re-bootstrapping prim from partitioned components
May 7 19:45:34 ns514282 mysqld: 150507 19:45:34 [Note] WSREP: view(view_id(PRIM,66b559a2,12) memb {
May 7 19:45:34 ns514282 mysqld: #01166b559a2,0
May 7 19:45:34 ns514282 mysqld: #011dc2b490d,0
May 7 19:45:34 ns514282 mysqld: } joined {
May 7 19:45:34 ns514282 mysqld: } left {
May 7 19:45:34 ns514282 mysqld: } partitioned {
May 7 19:45:34 ns514282 mysqld: })
May 7 19:45:34 ns514282 mysqld: 150507 19:45:34 [Note] WSREP: save pc into disk
May 7 19:45:34 ns514282 mysqld: 150507 19:45:34 [Note] WSREP: clear restored view
May 7 19:45:34 ns514282 mysqld: 150507 19:45:34 [Note] WSREP: gcomm: connected
May 7 19:45:34 ns514282 mysqld: 150507 19:45:34 [Note] WSREP: Changing maximum packet size to 64500, resulting msg size: 32636
May 7 19:45:34 ns514282 mysqld: 150507 19:45:34 [Note] WSREP: Shifting CLOSED -> OPEN (TO: 0)
May 7 19:45:34 ns514282 mysqld: 150507 19:45:34 [Note] WSREP: Opened channel 'bfm_cluster'
May 7 19:45:34 ns514282 mysqld: 150507 19:45:34 [Note] WSREP: Waiting for SST to complete.
May 7 19:45:34 ns514282 mysqld: 150507 19:45:34 [Note] WSREP: New COMPONENT: primary = yes, bootstrap = no, my_idx = 0, memb_num = 2
May 7 19:45:34 ns514282 mysqld: 150507 19:45:34 [Note] WSREP: STATE_EXCHANGE: sent state UUID: 279db665-f513-11e4-9149-aa318d13ebc4
May 7 19:45:34 ns514282 mysqld: 150507 19:45:34 [Note] WSREP: STATE EXCHANGE: sent state msg: 279db665-f513-11e4-9149-aa318d13ebc4
May 7 19:45:34 ns514282 mysqld: 150507 19:45:34 [Note] WSREP: STATE EXCHANGE: got state msg: 279db665-f513-11e4-9149-aa318d13ebc4 from 0 (sql1)
May 7 19:45:34 ns514282 mysqld: 150507 19:45:34 [Note] WSREP: STATE EXCHANGE: got state msg: 279db665-f513-11e4-9149-aa318d13ebc4 from 1 (sql3)
May 7 19:45:34 ns514282 mysqld: 150507 19:45:34 [Warning] WSREP: Quorum: No node with complete state:
May 7 19:45:34 ns514282 mysqld:
May 7 19:45:34 ns514282 mysqld:
May 7 19:45:34 ns514282 mysqld: #011Version : 3
May 7 19:45:34 ns514282 mysqld: #011Flags : 0x1
May 7 19:45:34 ns514282 mysqld: #011Protocols : 0 / 7 / 3
May 7 19:45:34 ns514282 mysqld: #011State : NON-PRIMARY
May 7 19:45:34 ns514282 mysqld: #011Prim state : NON-PRIMARY
May 7 19:45:34 ns514282 mysqld: #011Prim UUID : 00000000-0000-0000-0000-000000000000
May 7 19:45:34 ns514282 mysqld: #011Prim seqno : -1
May 7 19:45:34 ns514282 mysqld: #011First seqno : -1
May 7 19:45:34 ns514282 mysqld: #011Last seqno : -1
May 7 19:45:34 ns514282 mysqld: #011Prim JOINED : 0
May 7 19:45:34 ns514282 mysqld: #011State UUID : 279db665-f513-11e4-9149-aa318d13ebc4
May 7 19:45:34 ns514282 mysqld: #011Group UUID : 00000000-0000-0000-0000-000000000000
May 7 19:45:34 ns514282 mysqld: #011Name : 'sql1'
May 7 19:45:34 ns514282 mysqld: #011Incoming addr: '10.1.1.142:3306'
May 7 19:45:34 ns514282 mysqld:
May 7 19:45:34 ns514282 mysqld: #011Version : 3
May 7 19:45:34 ns514282 mysqld: #011Flags : 0x2
May 7 19:45:34 ns514282 mysqld: #011Protocols : 0 / 7 / 3
May 7 19:45:34 ns514282 mysqld: #011State : NON-PRIMARY
May 7 19:45:34 ns514282 mysqld: #011Prim state : SYNCED
May 7 19:45:34 ns514282 mysqld: #011Prim UUID : b65a0277-f50f-11e4-a916-dbeff5b65a2e
May 7 19:45:34 ns514282 mysqld: #011Prim seqno : 8
May 7 19:45:34 ns514282 mysqld: #011First seqno : -1
May 7 19:45:34 ns514282 mysqld: #011Last seqno : 0
May 7 19:45:34 ns514282 mysqld: #011Prim JOINED : 1
May 7 19:45:34 ns514282 mysqld: #011State UUID : 279db665-f513-11e4-9149-aa318d13ebc4
May 7 19:45:34 ns514282 mysqld: #011Group UUID : dc2be55b-f506-11e4-8748-4bd7f3fc795c
May 7 19:45:34 ns514282 mysqld: #011Name : 'sql3'
May 7 19:45:34 ns514282 mysqld: #011Incoming addr: '10.1.1.141:3306'
May 7 19:45:34 ns514282 mysqld:
May 7 19:45:34 ns514282 mysqld: 150507 19:45:34 [Note] WSREP: Full re-merge of primary b65a0277-f50f-11e4-a916-dbeff5b65a2e found: 1 of 1.
May 7 19:45:34 ns514282 mysqld: 150507 19:45:34 [Note] WSREP: Quorum results:
May 7 19:45:34 ns514282 mysqld: #011version = 3,
May 7 19:45:34 ns514282 mysqld: #011component = PRIMARY,
May 7 19:45:34 ns514282 mysqld: #011conf_id = 8,
May 7 19:45:34 ns514282 mysqld: #011members = 1/2 (joined/total),
May 7 19:45:34 ns514282 mysqld: #011act_id = 0,
May 7 19:45:34 ns514282 mysqld: #011last_appl. = -1,
May 7 19:45:34 ns514282 mysqld: #011protocols = 0/7/3 (gcs/repl/appl),
May 7 19:45:34 ns514282 mysqld: #011group UUID = dc2be55b-f506-11e4-8748-4bd7f3fc795c
May 7 19:45:34 ns514282 mysqld: 150507 19:45:34 [Note] WSREP: Flow-control interval: [23, 23]
May 7 19:45:34 ns514282 mysqld: 150507 19:45:34 [Note] WSREP: Shifting OPEN -> PRIMARY (TO: 0)
May 7 19:45:34 ns514282 mysqld: 150507 19:45:34 [Note] WSREP: State transfer required:
May 7 19:45:34 ns514282 mysqld: #011Group state: dc2be55b-f506-11e4-8748-4bd7f3fc795c:0
May 7 19:45:34 ns514282 mysqld: #011Local state: 00000000-0000-0000-0000-000000000000:-1
May 7 19:45:34 ns514282 mysqld: 150507 19:45:34 [Note] WSREP: New cluster view: global state: dc2be55b-f506-11e4-8748-4bd7f3fc795c:0, view# 9: Primary, number of nodes: 2, my index: 0, protocol version 3
May 7 19:45:34 ns514282 mysqld: 150507 19:45:34 [Warning] WSREP: Gap in state sequence. Need state transfer.
May 7 19:45:34 ns514282 mysqld: 150507 19:45:34 [Note] WSREP: Running: 'wsrep_sst_rsync --role 'joiner' --address '10.1.1.142' --auth 'cluster:password' --datadir '/var/lib/mysql/' --defaults-file '/etc/mysql/my.cnf' --parent '12278' --binlog '/var/log/mysql/mariadb-bin' '
May 7 19:45:34 ns514282 rsyncd[12428]: rsyncd version 3.0.9 starting, listening on port 4444
May 7 19:45:37 ns514282 mysqld: 150507 19:45:37 [Note] WSREP: (66b559a2, 'tcp://0.0.0.0:4567') turning message relay requesting off
May 7 19:45:47 ns514282 /usr/sbin/irqbalance: Load average increasing, re-enabling all cpus for irq balancing
May 7 19:45:57 ns514282 /usr/sbin/irqbalance: Load average increasing, re-enabling all cpus for irq balancing
May 7 19:46:02 ns514282 /USR/SBIN/CRON[16491]: (root) CMD (/usr/local/rtm/bin/rtm 50 > /dev/null 2> /dev/null)
May 7 19:46:03 ns514282 /etc/init.d/mysql[16711]: 0 processes alive and '/usr/bin/mysqladmin --defaults-file=/etc/mysql/debian.cnf ping' resulted in
May 7 19:46:03 ns514282 /etc/init.d/mysql[16711]: #007/usr/bin/mysqladmin: connect to server at 'localhost' failed
May 7 19:46:03 ns514282 /etc/init.d/mysql[16711]: error: 'Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (111 "Connection refused")'
May 7 19:46:03 ns514282 /etc/init.d/mysql[16711]: Check that mysqld is running and that the socket: '/var/run/mysqld/mysqld.sock' exists!
이 질문은 인터넷에 셀 수 없을 정도로 많다.답이 없는 사람도 있고해답을 가지고 있는 몇몇 사람들은
오류 2002(HY000): 소켓 '/var/run/mysqld/mysqld'를 통해 로컬 MySQL 서버에 연결할 수 없습니다.sock' (sock) - 디스크 공간이 꽉 차지 않습니다.
소켓 '/var/run/mysqld/mysqld'를 통해 로컬 MySQL 서버에 연결할 수 없습니다.sock' - 응답이 없습니다.그러나 코멘트에 따르면 mysql.sock은 존재하며 mysql.mysql 소유권을 가지고 있습니다.
오류 2002(HY000): 소켓 '/var/run/mysqld/mysqld'를 통해 로컬 MySQL 서버에 연결할 수 없습니다.sock' (2) - 서버가 설치되었지만 소켓이 올바른 위치에 있습니다.
또한 이것이 /var/run/mysqld의 권한 문제일 수도 있다는 것을 읽었지만, 이것을 확인하고 mysql.mysql 소유권을 부여했습니다.
다른 것은 이 문제를 되살리려는 시도입니다.어떤 방향이든 정말 감사합니다.
감사해요.
업데이트: 양쪽 노드의 my.cnf.기본 my.cnf 입니다.유일한 변경은 코멘트를 하는 것입니다.bind-address=127.0.0.1linedisplaces를 합니다.
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0
[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc_messages_dir = /usr/share/mysql
lc_messages = en_US
skip-external-locking
# bind-address = 127.0.0.1
max_connections = 100
connect_timeout = 5
wait_timeout = 600
max_allowed_packet = 16M
thread_cache_size = 128
sort_buffer_size = 4M
bulk_insert_buffer_size = 16M
tmp_table_size = 32M
max_heap_table_size = 32M
myisam_recover = BACKUP
key_buffer_size = 128M
table_open_cache = 400
myisam_sort_buffer_size = 512M
concurrent_insert = 2
read_buffer_size = 2M
read_rnd_buffer_size = 1M
query_cache_limit = 128K
query_cache_size = 64M
log_warnings = 2
slow_query_log_file = /var/log/mysql/mariadb-slow.log
long_query_time = 10
log_slow_verbosity = query_plan
log_bin = /var/log/mysql/mariadb-bin
log_bin_index = /var/log/mysql/mariadb-bin.index
expire_logs_days = 10
max_binlog_size = 100M
default_storage_engine = InnoDB
innodb_buffer_pool_size = 256M
innodb_log_buffer_size = 8M
innodb_file_per_table = 1
innodb_open_files = 400
innodb_io_capacity = 400
innodb_flush_method = O_DIRECT
[mysqldump]
quick
quote-names
max_allowed_packet = 16M
[mysql]
[isamchk]
key_buffer = 16M
!includedir /etc/mysql/conf.d/
업데이트 또한 테스트했습니다.노드를 정기적으로 기동하려고 하면(클러스터 없이 추가 설정 없이 기본값만) 동작합니다.
"wsrep_node_name"이 맞습니까?
[mysqld]
#mysql settings
bind-address=10.1.1.140
query_cache_size=0
query_cache_type=0
binlog_format=ROW
default_storage_engine=innodb
innodb_autoinc_lock_mode=2
innodb_doublewrite=1
#galery settings
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://10.1.1.139,10.1.1.140"
wsrep_sst_method=rsync
wsrep_cluster_name="sql_cluster"
wsrep_node_incoming_address=10.1.1.140
wsrep_sst_receive_address=10.1.1.140
wsrep_sst_auth=cluster:password
wsrep_node_address='10.1.1.140'
wsrep_node_name='sql1' <== ???
wsrep_on=ON
서버의 재기동을 추천합니다.
문제가 계속 발생하는 경우... 백업, 제거 및 다시 설치...
나하고는 이렇게 작동했어
사용되는 모든 IPS가 BIND에 준거하고 있는지 확인합니다.
언급URL : https://stackoverflow.com/questions/30114174/cant-connect-to-local-mysql-server-through-socket-var-run-mysqld-mysqld-sock
반응형
'itsource' 카테고리의 다른 글
| 열에서 고유한 값 선택 (0) | 2022.10.26 |
|---|---|
| 콜과 신청의 차이점은 무엇입니까? (0) | 2022.10.26 |
| Python에서 정수를 문자열로 변환 (0) | 2022.10.26 |
| 두 텍스트 문서 간의 유사성을 계산하는 방법은 무엇입니까? (0) | 2022.10.26 |
| 재귀적 콜라츠 추측 함수 프로그램이 출력을 제공하지 않습니다. (0) | 2022.10.26 |