{"id":181,"date":"2020-04-17T08:30:57","date_gmt":"2020-04-17T00:30:57","guid":{"rendered":"http:\/\/wp.vicds.com\/?p=181"},"modified":"2024-10-31T09:20:40","modified_gmt":"2024-10-31T01:20:40","slug":"mariadb-first-login","status":"publish","type":"post","link":"https:\/\/wp.vicds.com\/?p=181","title":{"rendered":"mariadb first login"},"content":{"rendered":"\n<p>&nbsp;\u597d\u7528\u5de5\u5177  <a href=\"https:\/\/www.adminer.org\/\" target=\"_blank\" rel=\"noreferrer noopener\">https:\/\/www.adminer.org\/<\/a><\/p>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-\"># mark bind address\nvim \/etc\/mysql\/mariadb.conf.d\/50-server.cnf\n#bind-address = 127.0.0.1\n\n# add password for root\nsudo mysqladmin --user=root password &quot;newpassword&quot;\n# \u4e0d\u986f\u793a\u5728\u547d\u4ee4\u5217\u4e0a\nsudo mysqladmin --user=root password\n\n# login mariadb\nsudo mysql -u root -p\n  use mysql;\n  GRANT ALL PRIVILEGES ON *.* TO &#039;root&#039;@&#039;192.168.1.%&#039; IDENTIFIED BY &#039;\u5bc6\u78bc&#039; WITH GRANT OPTION;\n  FLUSH PRIVILEGES;\n  exit;\n\n# update password\nUpdate user SET password=PASSWORD(&quot;\u5bc6\u78bc&quot;) WHERE user=&quot;\u4f7f\u7528\u8005&quot;;\n# mariadb 10.4+ \u4ee5\u5f8c\u8981\u7528\u9019\u6a23\u6539\nSET PASSWORD FOR &#039;\u4f7f\u7528\u8005&#039;@&#039;\u4f4d\u7f6e&#039; = PASSWORD(&#039;\u5bc6\u78bc&#039;);\n\n# restart mariadb\nsudo systemctl restart mariadb.service\n\n# create database unicode_ci\u5bb9\u91cf\u8f03\u5927\u4f46\u652f\u63f4\u8f03\u4f73, general_ci\u5247\u76f8\u53cd. utf8mb4\u624d\u662f4\u4f4d\u5143\u7684\u771f\u6b63utf8\nCREATE DATABASE mydatabase CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;\n# delete database\nDROP DATABASE mydatabase;\n# delete tables\nUSE DATABASE_NAME;\nDROP TABLE table_name;\n\u5982\u679c\u8981\u91cd\u6539\u8cc7\u6599\u5eab, \u8a18\u5f97\u5148\u5099\u4efd, \u901a\u5e38\u662f\u653e\u5728\u532f\u51fa\u5f8c\u7684\u4e0a\u65b9\nDROP DATABASE IF EXISTS db_name;\nCREATE DATABASE db_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;\nUSE db_name;\n\n# create user\nCREATE USER &#039;newuser&#039;@&#039;localhost&#039; IDENTIFIED BY &#039;user_password&#039;;\nGRANT ALL PRIVILEGES ON database_name.* TO &#039;newuser&#039;@&#039;localhost&#039;;\n\n\u6253\u904e\u7684\u6307\u4ee4\u6703\u88ab\u5b58\u5728 \/home\/username\/.mysql_history \u88e1, \u8981\u5c0f\u5fc3.\n\n\u5099\u4efd\u8207\u9084\u539f\n# mysqldump -u root -p --all-databases --lock-tables=false &gt; backup.sql;\n# mysqldump -h hostname -u root -p database_name &gt; backup.sql;\n# mysqldump -h hostname -u root -pPASSWORD database_name &gt; backup.sql;\n\u53ea\u5099\u4efd\u67d0\u5e7e\u500b\u8868\n# mysqldump -h hostanme -u root -p database_name t1 t2 t3 &gt; backup_tables.sql\n\u6392\u9664\u67d0\u5e7e\u500b\u8868\n# mysqldump -h hostname -u root -p database_name --ignore-table=Table1 --ignore-table=Table2 &gt; backup.sql;\n\u9084\u539f\n# mysql -u root -p &lt; backup.sql\n\n\u5fd8\u8a18\u5bc6\u78bc\n# \/etc\/init.d\/mysql stop\n# mysqld_safe --skip-grant-tables &amp;\n# mysql -u root\nmysql&gt; use mysql;\nmysql&gt; UPDATE user SET Password=PASSWORD(&quot;password&quot;) WHERE User=&#039;root&#039;;\nmysql&gt; flush privileges;\nmysql&gt; quit\n# \/etc\/init.d\/mysql start\n\n# \u6aa2\u67e5\u4f7f\u7528\u8005\u6b0a\u9650\nSELECT user,host FROM db WHERE db=&#039;name&#039;;\nSELECT user,host FROM tables_priv WHERE db=&#039;name&#039;;\nSELECT user,host FROM columns_priv WHERE db=&#039;name&#039;;\nSELECT user,host FROM procs_priv WHERE db=&#039;name&#039;;\n\n# \u6709\u6642\u5019\u4f7f\u7528\u8005\u4e0d\u80fd\u767b\u5165\n# \u4e3b\u8981\u662f\u88abplugin unix_socket\u9019\u500b\u7684\u554f\u984c\nsudo mysql -uroot -p\nSELECT user, plugin FROM mysql.user;\n# \u6709\u5c31\u6e05\u6389\nUPDATE mysql.user SET plugin=&quot;&quot;;\n# \u52a0\u4e0aroot\u5bc6\u78bc\nUPDATE mysql.user SET password=PASSWORD(&quot;your_new_password&quot;) WHERE user=&quot;root&quot;;\n# \u5237\u65b0\u4e00\u4e0b\nFLUSH PRIVILEGES;\n\n# \u67e5\u76ee\u524d\u7de8\u78bc\u8a2d\u5b9a\nSHOW VARIABLES LIKE &#039;collation%&#039;;\n\n# \u67e5\u8a62\u7de8\u78bc\nSELECT \n    default_character_set_name, \n    default_collation_name \nFROM information_schema.schemata \nWHERE schema_name = &#039;DATABASE_NAME&#039;;\n\n# \u67e5\u8a62\u8868\u7de8\u78bc\nSELECT \n    table_schema, \n    table_name, \n    table_collation \nFROM information_schema.tables \nWHERE table_schema = &#039;TABLE_NAME&#039;;<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"<p>&nbsp;\u597d\u7528\u5de5\u5177 https:\/\/www [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[16],"tags":[],"class_list":["post-181","post","type-post","status-publish","format-standard","hentry","category-sql"],"_links":{"self":[{"href":"https:\/\/wp.vicds.com\/index.php?rest_route=\/wp\/v2\/posts\/181","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/wp.vicds.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/wp.vicds.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/wp.vicds.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/wp.vicds.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=181"}],"version-history":[{"count":29,"href":"https:\/\/wp.vicds.com\/index.php?rest_route=\/wp\/v2\/posts\/181\/revisions"}],"predecessor-version":[{"id":2364,"href":"https:\/\/wp.vicds.com\/index.php?rest_route=\/wp\/v2\/posts\/181\/revisions\/2364"}],"wp:attachment":[{"href":"https:\/\/wp.vicds.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=181"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/wp.vicds.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=181"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/wp.vicds.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=181"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}