Mysql user permission assignment and master-slave synchronous Replication

Source: Internet
Author: User

Mysql user permission assignment and master-slave synchronous Replication
Grant wgdp user query permission: grant select on wg_dp. * to 'wgdp '@' % 'identified by 'weigou123'; grant all privileges on *. * to 'yangshao' @ '%' identified by 'weigou123' query other mysql user permissions: show grants for wgdp; cancel wgdp User Permissions: revoke all on *. * from wgdp; PS: grant: After the revoke user permission is granted, the permission takes effect only when the user reconnects to the MySQL database. Permission range: 1. the select, insert, update, and delete permissions allow you to perform operations on an existing table in a database. They are basic permissions.
2. alter permission allows you to use ALTER TABLE
3. the create and drop permissions allow you to create new databases and tables, or discard (delete) existing databases and tables. If you grant the drop permission of the mysql database to a user, this user can discard the database that stores the MySQL access permission! After mysql5.5, some of the master-slave configuration fields in the mysql configuration file my. cnf have been deprecated. To enable the master-slave configuration, follow these steps:

1. Preparations: There are two linux Hosts

Master: 10.209.112.58 the username of mysql5.5 master is root, no password,

Slave: 10.46.169.62 the username of mysql5.5 slave is root, no password, and the account used by slave to log on to the master for synchronization is wgdp_syc;

Allow slave TO log on to the master: grant all privileges on *. * TO 'wgdp _ syc' @ '10. 46.169.62 'identified by 'syc1qaz2wsx' with grant option; flush privileges;

2. master: Configure my. cnf: [mysqld] # master configureserver-id = 1log-bin = mysql-binbinlog-do-db = masterbinlog-ignore-db = mysql # master configuredatadir =/var/lib/mysqlsocket =/var/ lib/mysql. sock

The maser id should be 1, indicating the log directory, synchronized database, and databases that cannot be synchronized

Then add an account on the Master for synchronization, as shown below:
Grant replication slave on *. * TO rep@192.168.74.227 identified by 'hello ';
If you want to have the permission to execute the "load table from master" or "load data from master" Statement on Slave, you must grant the Global FILE and SELECT permissions:
Grant file, SELECT, replication slave on *. * TO rep@192.168.74.227 identified by 'hello ';
Next, back up the data on the Master, first execute the following SQL statement:
Flush tables with read lock;
Then Package the synchronized data tar, and then scp to the machine on which 227 is located. decompress the package and pay attention to permission issues.
After completing the steps, run
UNLOCK TABLES

3. from: Configure my. cnf: [mysqld] # configure master-slaveserver-id = 2 # master-host = 192.168.74.225 # master-user = rep # master-password = helloreplicate-ignore-db = mysqlreplicate-do-db = master # configure master-slave notice that the three fields of master-host in slave configuration have been commented out, this configuration has been deprecated since 5.5. After logging on TO the mysql Server Load balancer instance, set the three fields and run the following statement: mysql> change master to MASTER_HOST = '10. 209.112.58 ', MASTER_USER = 'wgdp _ syc', MASTER_PASSWORD = 'syc1qaz2wsx', MASTER_LOG_FILE = 'mysql-bin.20170', MASTER_LOG_POS = 0; 4. after starting the master-SLAVE mysql instance, run the following command to check whether the problem exists: SLAVE Database: mysql> show slave status \ G;
  1. Slave_IO_Running: yes
  2. Slave_ SQL _Running: Yes, even if it is similar to mysql database synchronization error, Skip: mysql> slave stop; Query OK, 0 rows affected (0.01 sec) mysql> set global SQL _SLAVE_SKIP_COUNTER = 1; Query OK, 0 rows affected (0.00 sec) mysql> slave start; Query OK, 0 rows affected (0.00 sec)

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.