keystone同步时无法连接数据库的失败解决方法

问题：

root@swift:/etc/mysql/mariadb.conf.d# su -s /bin/sh -c "keystone-manage db_sync" keystone2017-05-17 21:38:28.105 5553 WARNING oslo_db.sqlalchemy.engines [-] SQL connection failed. 10 attempts left.2017-05-17 21:38:38.107 5553 WARNING oslo_db.sqlalchemy.engines [-] SQL connection failed. 9 attempts left.2017-05-17 21:38:48.117 5553 WARNING oslo_db.sqlalchemy.engines [-] SQL connection failed. 8 attempts left.2017-05-17 21:38:58.124 5553 WARNING oslo_db.sqlalchemy.engines [-] SQL connection failed. 7 attempts left.2017-05-17 21:39:08.130 5553 WARNING oslo_db.sqlalchemy.engines [-] SQL connection failed. 6 attempts left.2017-05-17 21:39:18.138 5553 WARNING oslo_db.sqlalchemy.engines [-] SQL connection failed. 5 attempts left.2017-05-17 21:39:28.144 5553 WARNING oslo_db.sqlalchemy.engines [-] SQL connection failed. 4 attempts left.2017-05-17 21:39:38.151 5553 WARNING oslo_db.sqlalchemy.engines [-] SQL connection failed. 3 attempts left.2017-05-17 21:39:48.158 5553 WARNING oslo_db.sqlalchemy.engines [-] SQL connection failed. 2 attempts left.2017-05-17 21:39:58.167 5553 WARNING oslo_db.sqlalchemy.engines [-] SQL connection failed. 1 attempts left.2017-05-17 21:40:08.171 5553 CRITICAL keystone [-] DBConnectionError: (pymysql.err.OperationalError) (2003, "Can't connect to MySQL server on 'swift' ([Errno 111] Connection refused)")2017-05-17 21:40:08.171 5553 ERROR keystone Traceback (most recent call last):2017-05-17 21:40:08.171 5553 ERROR keystone  File "/usr/bin/keystone-manage", line 10, in2017-05-17 21:40:08.171 5553 ERROR keystone    sys.exit(main())2017-05-17 21:40:08.171 5553 ERROR keystone  File "/usr/lib/python2.7/dist-packages/keystone/cmd/manage.py", line 47, in main2017-05-17 21:40:08.171 5553 ERROR keystone    cli.main(argv=sys.argv, config_files=config_files)2017-05-17 21:40:08.171 5553 ERROR keystone  File "/usr/lib/python2.7/dist-packages/keystone/cmd/cli.py", line 1024, in main2017-05-17 21:40:08.171 5553 ERROR keystone    CONF.command.cmd_class.main()2017-05-17 21:40:08.171 5553 ERROR keystone  File "/usr/lib/python2.7/dist-packages/keystone/cmd/cli.py", line 403, in main2017-05-17 21:40:08.171 5553 ERROR keystone    migration_helpers.sync_database_to_version(extension, version)2017-05-17 21:40:08.171 5553 ERROR keystone  File "/usr/lib/python2.7/dist-packages/keystone/common/sql/migration_helpers.py", line 210, in sync_database_to_version2017-05-17 21:40:08.171 5553 ERROR keystone    _sync_common_repo(version)2017-05-17 21:40:08.171 5553 ERROR keystone  File "/usr/lib/python2.7/dist-packages/keystone/common/sql/migration_helpers.py", line 132, in _sync_common_repo2017-05-17 21:40:08.171 5553 ERROR keystone    with sql.session_for_write() as session:2017-05-17 21:40:08.171 5553 ERROR keystone  File "/usr/lib/python2.7/contextlib.py", line 17, in __enter__2017-05-17 21:40:08.171 5553 ERROR keystone    return self.gen.next()2017-05-17 21:40:08.171 5553 ERROR keystone  File "/usr/lib/python2.7/dist-packages/oslo_db/sqlalchemy/enginefacade.py", line 759, in _transaction_scope2017-05-17 21:40:08.171 5553 ERROR keystone    allow_async=self._allow_async) as resource:2017-05-17 21:40:08.171 5553 ERROR keystone  File "/usr/lib/python2.7/contextlib.py", line 17, in __enter__2017-05-17 21:40:08.171 5553 ERROR keystone    return self.gen.next()2017-05-17 21:40:08.171 5553 ERROR keystone  File "/usr/lib/python2.7/dist-packages/oslo_db/sqlalchemy/enginefacade.py", line 491, in _session2017-05-17 21:40:08.171 5553 ERROR keystone    bind=self.connection, mode=self.mode)2017-05-17 21:40:08.171 5553 ERROR keystone  File "/usr/lib/python2.7/dist-packages/oslo_db/sqlalchemy/enginefacade.py", line 272, in _create_session2017-05-17 21:40:08.171 5553 ERROR keystone    self._start()2017-05-17 21:40:08.171 5553 ERROR keystone  File "/usr/lib/python2.7/dist-packages/oslo_db/sqlalchemy/enginefacade.py", line 338, in _start2017-05-17 21:40:08.171 5553 ERROR keystone    engine_args, maker_args)2017-05-17 21:40:08.171 5553 ERROR keystone  File "/usr/lib/python2.7/dist-packages/oslo_db/sqlalchemy/enginefacade.py", line 362, in _setup_for_connection2017-05-17 21:40:08.171 5553 ERROR keystone    sql_connection=sql_connection, **engine_kwargs)2017-05-17 21:40:08.171 5553 ERROR keystone  File "/usr/lib/python2.7/dist-packages/oslo_db/sqlalchemy/engines.py", line 152, in create_engine2017-05-17 21:40:08.171 5553 ERROR keystone    test_conn = _test_connection(engine, max_retries, retry_interval)2017-05-17 21:40:08.171 5553 ERROR keystone  File "/usr/lib/python2.7/dist-packages/oslo_db/sqlalchemy/engines.py", line 334, in _test_connection2017-05-17 21:40:08.171 5553 ERROR keystone    six.reraise(type(de_ref), de_ref)2017-05-17 21:40:08.171 5553 ERROR keystone  File "", line 2, in reraise

解决方法：

使用root登录MySQL

MariaDB [(none)]> show variables like "%char%";

+--------------------------+----------------------------+

| Variable_name            | Value                      |

+--------------------------+----------------------------+

| character_set_client    | utf8mb4                    |

| character_set_connection | utf8mb4                    |

| character_set_database  | utf8mb4                    |

| character_set_filesystem | binary                    |

| character_set_results    | utf8mb4                    |

| character_set_server    | utf8mb4                    |

| character_set_system    | utf8                      |

| character_sets_dir      | /usr/share/mysql/charsets/ |

+--------------------------+----------------------------+

8 rows in set (0.02 sec)

MariaDB [(none)]>exit

修改/etc/mysql/mariadb.conf.d/目录下的文件“50-client.cnf 50-mysql-clients.cnf   50-server.cnf”

将其中的utf8mb4修改为utf8

mariadb 修改允许远程访问

sed -i 's/127.0.0.1/0.0.0.0/g' /etc/mysql/mariadb.conf.d/50-server.cnf

重启mysql服务

service mysql restart

再次进入MySQL

MariaDB [(none)]> show variables like "%char%";

+--------------------------+----------------------------+

| Variable_name            | Value                      |

+--------------------------+----------------------------+

| character_set_client    | utf8                      |

| character_set_connection | utf8                      |

| character_set_database  | utf8                      |

| character_set_filesystem | binary                    |

| character_set_results    | utf8                      |

| character_set_server    | utf8                      |

| character_set_system    | utf8                      |

| character_sets_dir      | /usr/share/mysql/charsets/ |

+--------------------------+----------------------------+

此时还需将原有的数据库keystone删除再重新创建

MariaDB [(none)]>drop database keystone;

Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> CREATE DATABASE keystone;

Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> GRANT ALL PRIVILEGES ON keystone.* TO 'keystone'@'%' IDENTIFIED BY '8782';

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> GRANT ALL PRIVILEGES ON keystone.* TO 'keystone'@'localhost' IDENTIFIED BY '8782';

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> exit

Bye

再进行同步数据库

root@swift:/home/swift# su -s /bin/sh -c "keystone-manage db_sync" keystone

2017-05-17 21:50:35.746 2708 INFO migrate.versioning.api [-] 66 -> 67...

2017-05-17 21:50:36.418 2708 INFO migrate.versioning.api [-] done

2017-05-17 21:50:36.419 2708 INFO migrate.versioning.api [-] 67 -> 68...

2017-05-17 21:50:36.435 2708 INFO migrate.versioning.api [-] done

2017-05-17 21:50:36.435 2708 INFO migrate.versioning.api [-] 68 -> 69...

2017-05-17 21:50:36.453 2708 INFO migrate.versioning.api [-] done

2017-05-17 21:50:36.454 2708 INFO migrate.versioning.api [-] 69 -> 70...

2017-05-17 21:50:36.471 2708 INFO migrate.versioning.api [-] done

2017-05-17 21:50:36.471 2708 INFO migrate.versioning.api [-] 70 -> 71...

2017-05-17 21:50:36.489 2708 INFO migrate.versioning.api [-] done

2017-05-17 21:50:36.489 2708 INFO migrate.versioning.api [-] 71 -> 72...

2017-05-17 21:50:36.511 2708 INFO migrate.versioning.api [-] done

2017-05-17 21:50:36.512 2708 INFO migrate.versioning.api [-] 72 -> 73...

2017-05-17 21:50:36.572 2708 INFO migrate.versioning.api [-] done

2017-05-17 21:50:36.572 2708 INFO migrate.versioning.api [-] 73 -> 74...

2017-05-17 21:50:36.617 2708 INFO migrate.versioning.api [-] done

2017-05-17 21:50:36.617 2708 INFO migrate.versioning.api [-] 74 -> 75...

2017-05-17 21:50:36.647 2708 INFO migrate.versioning.api [-] done

2017-05-17 21:50:36.647 2708 INFO migrate.versioning.api [-] 75 -> 76...

2017-05-17 21:50:36.663 2708 INFO migrate.versioning.api [-] done

2017-05-17 21:50:36.663 2708 INFO migrate.versioning.api [-] 76 -> 77...

2017-05-17 21:50:36.681 2708 INFO migrate.versioning.api [-] done

2017-05-17 21:50:36.681 2708 INFO migrate.versioning.api [-] 77 -> 78...

2017-05-17 21:50:36.699 2708 INFO migrate.versioning.api [-] done

2017-05-17 21:50:36.700 2708 INFO migrate.versioning.api [-] 78 -> 79...

2017-05-17 21:50:36.718 2708 INFO migrate.versioning.api [-] done

2017-05-17 21:50:36.719 2708 INFO migrate.versioning.api [-] 79 -> 80...

2017-05-17 21:50:36.736 2708 INFO migrate.versioning.api [-] done

2017-05-17 21:50:36.738 2708 INFO migrate.versioning.api [-] 80 -> 81...

2017-05-17 21:50:36.768 2708 INFO migrate.versioning.api [-] done

2017-05-17 21:50:36.768 2708 INFO migrate.versioning.api [-] 81 -> 82...

2017-05-17 21:50:36.852 2708 INFO migrate.versioning.api [-] done

2017-05-17 21:50:36.852 2708 INFO migrate.versioning.api [-] 82 -> 83...

2017-05-17 21:50:36.924 2708 INFO migrate.versioning.api [-] done

2017-05-17 21:50:36.925 2708 INFO migrate.versioning.api [-] 83 -> 84...

2017-05-17 21:50:36.961 2708 INFO migrate.versioning.api [-] done

2017-05-17 21:50:36.962 2708 INFO migrate.versioning.api [-] 84 -> 85...

2017-05-17 21:50:37.022 2708 INFO migrate.versioning.api [-] done

2017-05-17 21:50:37.023 2708 INFO migrate.versioning.api [-] 85 -> 86...

2017-05-17 21:50:37.054 2708 INFO migrate.versioning.api [-] done

2017-05-17 21:50:37.055 2708 INFO migrate.versioning.api [-] 86 -> 87...

2017-05-17 21:50:37.119 2708 INFO migrate.versioning.api [-] done

2017-05-17 21:50:37.120 2708 INFO migrate.versioning.api [-] 87 -> 88...

2017-05-17 21:50:37.160 2708 INFO migrate.versioning.api [-] done

2017-05-17 21:50:37.160 2708 INFO migrate.versioning.api [-] 88 -> 89...

2017-05-17 21:50:37.199 2708 INFO migrate.versioning.api [-] done

2017-05-17 21:50:37.199 2708 INFO migrate.versioning.api [-] 89 -> 90...

2017-05-17 21:50:37.243 2708 INFO migrate.versioning.api [-] done

2017-05-17 21:50:37.244 2708 INFO migrate.versioning.api [-] 90 -> 91...

2017-05-17 21:50:37.319 2708 INFO migrate.versioning.api [-] done

2017-05-17 21:50:37.320 2708 INFO migrate.versioning.api [-] 91 -> 92...

2017-05-17 21:50:37.396 2708 INFO migrate.versioning.api [-] done

2017-05-17 21:50:37.397 2708 INFO migrate.versioning.api [-] 92 -> 93...

2017-05-17 21:50:37.476 2708 INFO migrate.versioning.api [-] done

2017-05-17 21:50:37.476 2708 INFO migrate.versioning.api [-] 93 -> 94...

2017-05-17 21:50:37.546 2708 INFO migrate.versioning.api [-] done

2017-05-17 21:50:37.547 2708 INFO migrate.versioning.api [-] 94 -> 95...

2017-05-17 21:50:37.606 2708 INFO migrate.versioning.api [-] done

2017-05-17 21:50:37.607 2708 INFO migrate.versioning.api [-] 95 -> 96...

2017-05-17 21:50:37.626 2708 INFO migrate.versioning.api [-] done

2017-05-17 21:50:37.627 2708 INFO migrate.versioning.api [-] 96 -> 97...

2017-05-17 21:50:37.652 2708 INFO migrate.versioning.api [-] done

root@swift:/home/swift#