Mysql user permission assignment and master-slave synchronous replication, mysql user permission

Mysql user permission assignment and master-slave synchronous replication, mysql user permission
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.

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)
Mysql database master-slave synchronization: Create a mysql user on the master server, allow the user to log on from the specified host, and grant File Permissions

This is obviously due to the asymmetry of the punctuation server. Why are there three single nodes and how can we use the last comma !!!!!!!!!!!!!!!!

How many databases can be synchronized at most for Mysql master-slave replication? What are the best suggestions?

In theory, master-slave replication supports an infinite number of slave databases. In practice, it is affected by the bandwidth and read/write capacity of the server.
Please refer to the suggestions in the mysql official manual:
Theoretically, you can use a single master server/multiple slave server to expand the system by adding more slave servers until the network bandwidth is used up, or your update load has grown to a point that the master server cannot handle.

Before you get the benefits, you need to know the query mode to determine how many slave servers can be used and how much performance can be improved for your site, in addition, the relationship between the throughput and write (max_writes) throughput of a typical master server and the read (read per second, or max_reads) from the slave server should be determined based on experience. Through a hypothetical system with replication, this example provides a very simple computing result.

Assuming that the system load includes 10% write and 90% read, and the benchmark test shows that max_reads is 1200-2 × max_writes. In other words, if there is no write operation, the system can perform 1,200 read operations per second. The average write operation is twice the average read operation time, and the relationship is linear. We assume that the master server has the same performance as each slave server, and we have one master server and N slave servers. For each server (master server or slave server), we have:

Reads = 1200-2 × writes

Reads = 9 × writes/(N + 1) (read is separated, but written to all servers)

9 × writes/(N + 1) + 2 × writes = 1200

Writes = 1200/(2 + 9/(N + 1 ))

The final equation indicates the maximum write operations of N slave servers. Assuming that the maximum possible read rate is 1,200 times per minute, the ratio of read operations to write operations is 9.

The following conclusions can be obtained from the above analysis:

· If N = 0 (this indicates there is no replication), the system can process about 1200/11 = 109 write operations per second.

· If N = 1, 184 write operations are performed per second.

· If N = 8, 400 write operations are performed per second.

· If N = 17, 480 write operations are performed per second.