mysql

1
docker pull mysql:5.7.33

1
mkdir /home/mysql

1
docker run -p 3306:3306 --name mysql -v /home/mysql/conf:/etc/mysql -v /home/mysql/logs:/var/log/mysql -v /home/mysql/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7.33

1
show variables like '%log_bin%'

1
cd /home/mysql/conf

1
2
3
4
[mysqld]
log-bin=/var/log/mysql/mysql-bin
binlog_format=ROW
server-id=123454

1
docker restart mysql

1
chmod -R 764 *

1
show variables like '%log_bin%'

1
docker pull canal/canal-server

1
docker run -d --name canal-server -p 11111:11111 canal/canal-server

1
2
mkdir /home/canal/conf
mkdir /home/canal/logs

1
docker cp canal-server:/home/admin/canal-server/conf /home/canal/

1
2
3
4
5
6
7
8
# 数据库连接信息
canal.instance.master.address=127.0.0.1:3306
canal.instance.dbUsername=canal
canal.instance.dbPassword=canal
# 监听表
canal.instance.filter.regex=blog_tag,ppt_tag
# mq的topic
canal.mq.topic=mysql

1
2
3
4
# tcp, kafka, RocketMQ
canal.serverMode = kafka
canal.zkServers =note01:2181,note02:2181,note03:2181
canal.mq.servers = note01:9092,note02:9092,note03:9092

CREATE USER canal IDENTIFIED BY 'canal';
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'canal'@'%';
FLUSH PRIVILEGES;

1
docker rm -f canal-server

1
docker run -d --name canal-server -v /home/canal/conf:/home/admin/canal-server/conf -v /home/canal/logs:/home/admin/canal-server/logs -p 11111:11111 canal/canal-server

1
CREATE USER 用户名 IDENTIFIED BY '密码';

1
GRANT privileges ON databasename.tablename TO 'username'@'host'

1
GRANT ALL ON *.* TO 'hk'@'%';

1
REVOKE privilege ON databasename.tablename FROM 'username'@'host';

1
DROP USER 'username'@'host';

1
SELECT * FROM mysql.user;

<h2><a id="_0"></a>安装</h2> <p>采用docker安装方式</p> <ol> <li>下载镜像</li> </ol> <pre><div class="hljs"><code class="lang-shell">docker pull mysql:5.7.33 </code></div></pre> <ol start="2"> <li>建立映射目录</li> </ol> <pre><div class="hljs"><code class="lang-shell">mkdir /home/mysql </code></div></pre> <ol start="3"> <li>运行mysql</li> </ol> <pre><div class="hljs"><code class="lang-shell">docker run -p 3306:3306 --name mysql -v /home/mysql/conf:/etc/mysql -v /home/mysql/logs:/var/log/mysql -v /home/mysql/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7.33 </code></div></pre> <h2><a id="binlog_21"></a>启动binlog</h2> <p>连接mysql查询是否启动binlog，执行sql：</p> <pre><div class="hljs"><code class="lang-sql"><span class="hljs-keyword">show</span> <span class="hljs-keyword">variables</span> <span class="hljs-keyword">like</span> <span class="hljs-string">'%log_bin%'</span> </code></div></pre> <p>log_bin的值：OFF表示关，ON表示开</p> <p>如果没开按照以下流程开启binlog</p> <ol> <li>进入挂载目录conf</li> </ol> <pre><div class="hljs"><code class="lang-shell">cd /home/mysql/conf </code></div></pre> <ol start="2"> <li>修改目录下my.cnf文件，如果没有用vi新建(在window新建可能出现编码问题)</li> </ol> <pre><div class="hljs"><code class="lang-shell">[mysqld] log-bin=/var/log/mysql/mysql-bin binlog_format=ROW server-id=123454 </code></div></pre> <blockquote> <p>如果是新建的必须要加上[mysqld]<br /> log-bin的值是生成binlog目录，路径最后面的mysql-bin是生成的文件名，MySQL会自动在文件名后面加后缀，/var/log/mysql这个目录要设置成容器里面的路径，不是宿主机，不是宿主机，不是宿主机<br /> binlog_format有三种不同模式：Mixed,Statement,Row我们用Row<a href="http://www.gameboys.cn/article/162" target="_blank">详情</a><br /> server-id集群id随便写</p> </blockquote> <ol start="3"> <li>重启mysql</li> </ol> <pre><div class="hljs"><code class="lang-shell">docker restart mysql </code></div></pre> <ol start="4"> <li>如果重启报错，查询报错日志，可能是没权限，mysql下问价加权限即可</li> </ol> <pre><div class="hljs"><code class="lang-shell">chmod -R 764 * </code></div></pre> <ol start="5"> <li>执行sql查看是否开启成功</li> </ol> <pre><div class="hljs"><code class="lang-sql"><span class="hljs-keyword">show</span> <span class="hljs-keyword">variables</span> <span class="hljs-keyword">like</span> <span class="hljs-string">'%log_bin%'</span> </code></div></pre> <h2><a id="canal_65"></a>安装canal</h2> <ol> <li>下载镜像</li> </ol> <pre><div class="hljs"><code class="lang-shell">docker pull canal/canal-server </code></div></pre> <ol start="2"> <li>启动镜像,把配置文件复制出来</li> </ol> <pre><div class="hljs"><code class="lang-shell">docker run -d --name canal-server -p 11111:11111 canal/canal-server </code></div></pre> <ol start="3"> <li>新建目录</li> </ol> <pre><div class="hljs"><code class="lang-shell">mkdir /home/canal/conf mkdir /home/canal/logs </code></div></pre> <ol start="4"> <li>复制配置文件</li> </ol> <pre><div class="hljs"><code class="lang-shell">docker cp canal-server:/home/admin/canal-server/conf /home/canal/ </code></div></pre> <ol start="5"> <li>修改配置文件/home/canal/conf/example/instance.properties</li> </ol> <pre><div class="hljs"><code class="lang-shell"><span class="hljs-meta">#</span><span class="bash"> 数据库连接信息</span> canal.instance.master.address=127.0.0.1:3306 canal.instance.dbUsername=canal canal.instance.dbPassword=canal <span class="hljs-meta">#</span><span class="bash"> 监听表</span> canal.instance.filter.regex=blog_tag,ppt_tag <span class="hljs-meta">#</span><span class="bash"> mq的topic</span> canal.mq.topic=mysql </code></div></pre> <ol start="6"> <li>修改/home/canal/conf/canal.properties</li> </ol> <pre><div class="hljs"><code class="lang-shell"><span class="hljs-meta">#</span><span class="bash"> tcp, kafka, RocketMQ</span> canal.serverMode = kafka canal.zkServers =note01:2181,note02:2181,note03:2181 canal.mq.servers = note01:9092,note02:9092,note03:9092 </code></div></pre> <ol start="7"> <li>mysql新增用户</li> </ol> <pre><code class="lang-mysql">CREATE USER canal IDENTIFIED BY 'canal'; GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'canal'@'%'; FLUSH PRIVILEGES; </code></pre> <ol start="8"> <li>删除canal</li> </ol> <pre><div class="hljs"><code class="lang-shell">docker rm -f canal-server </code></div></pre> <ol start="9"> <li>启动canal</li> </ol> <pre><div class="hljs"><code class="lang-shell">docker run -d --name canal-server -v /home/canal/conf:/home/admin/canal-server/conf -v /home/canal/logs:/home/admin/canal-server/logs -p 11111:11111 canal/canal-server </code></div></pre> <h2><a id="_131"></a>遇到问题</h2> <h3><a id="_132"></a>启动报没权限错误</h3> <p>删除容器，启动的时候加参数 --privileged=true</p> <h2><a id="sql_135"></a>常用sql</h2> <h3><a id="_136"></a>创建用户</h3> <pre><div class="hljs"><code class="lang-sql"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">USER</span> 用户名 <span class="hljs-keyword">IDENTIFIED</span> <span class="hljs-keyword">BY</span> <span class="hljs-string">'密码'</span>; </code></div></pre> <h3><a id="_141"></a>授权</h3> <p>命令：</p> <pre><div class="hljs"><code class="lang-sql"><span class="hljs-keyword">GRANT</span> <span class="hljs-keyword">privileges</span> <span class="hljs-keyword">ON</span> databasename.tablename <span class="hljs-keyword">TO</span> <span class="hljs-string">'username'</span>@<span class="hljs-string">'host'</span> </code></div></pre> <ul> <li>privileges：用户的操作权限，如SELECT，INSERT，UPDATE等，如果要授予所的权限则使用ALL</li> <li>databasename：数据库名</li> <li>tablename：表名，如果要授予该用户对所有数据库和表的相应操作权限则可用<em>表示，如</em>.*</li> <li>host：表示本地用户，若给远程用户授权使用 %</li> </ul> <p>示例：</p> <pre><div class="hljs"><code class="lang-sql"><span class="hljs-keyword">GRANT</span> ALL <span class="hljs-keyword">ON</span> *.* <span class="hljs-keyword">TO</span> <span class="hljs-string">'hk'</span>@<span class="hljs-string">'%'</span>; </code></div></pre> <h3><a id="_159"></a>撤销用户权限</h3> <p>命令：</p> <pre><div class="hljs"><code class="lang-sql"><span class="hljs-keyword">REVOKE</span> privilege <span class="hljs-keyword">ON</span> databasename.tablename <span class="hljs-keyword">FROM</span> <span class="hljs-string">'username'</span>@<span class="hljs-string">'host'</span>; </code></div></pre> <h3><a id="_165"></a>删除用户</h3> <pre><div class="hljs"><code class="lang-sql"><span class="hljs-keyword">DROP</span> <span class="hljs-keyword">USER</span> <span class="hljs-string">'username'</span>@<span class="hljs-string">'host'</span>; </code></div></pre> <h3><a id="_171"></a>查看所有用户</h3> <pre><div class="hljs"><code class="lang-sql"><span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> mysql.user; </code></div></pre>