Create a MySQL database with Ansible

Photo Courtesy Of Pexels.com

Ansible is ideal for deploying MySQL on a server without any extra effort on our part. Let's see how this happens together. Even if you have no experience with Ansible yet, you can easily figure it out. Docker Ansible, .

MySQL, :

1. hosts ( , ).
2. . , .
3. SQL. , , .
4. .

.

1. hosts

, localhost. IP-, . hosts ( ): touch hosts

[mysql]
127.0.0.1
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

2. MySQL

: , , , . - , db-server-playbook.yml:

touch db-server-playbook.yml
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

, ( 2 hosts). mysql_root_password 4 5. . , , ansible-vault :

: — .

1 ---   
2 - hosts: mysql   
3   
4   vars:   
5     mysql_root_password: password   
6
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

, . mysql apt Ubuntu Docker. package yum, Centos:

7   tasks:  
8    - name: install mysql    
9      apt: 
10    name: mysql 
11    update_cache: yes 
12      cache_valid_time: 3600
13      state: present
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

, , mysql :

14     - name: start up the mysql service  
15       shell: "service mysql start"  
16     - name: ensure mysql is enabled to run on startup  
17       service: 
18         name:mysql 
19         state:started 
20         enabled:true
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

mysql. mysql ( Docker), , localhost 127.0.0.1. mysql_user , , with_item:

21     - name: update mysql root password for all root accounts  
22       mysql_user:  
23         name: root  
24         host: "{{ item }}"  
25         password: "{{ mysql_root_password }}"  
26         login_user: root  
27         login_password: "{{ mysql_root_password }}"  
28         check_implicit_admin: yes  
29         priv: "*.*:ALL,GRANT"  
30       with_items:  
31       - "{{ ansible_hostname }}"  
32       - 127.0.0.1  
33       - ::1  
34       - localhost
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

MySQL mysql_db, 37-40, 46-51 SQL, :

35     - name: create a new database  
36      mysql_db: 
37     name: testdb 
38     state: present 
39     login_user: root 
40     login_password: "{{ mysql_root_password }}"  
41     - name: add sample data to database  
42       copy: 
43         src:dump.sql 
44         dest:/tmp/dump.sql  
45     - name: insert sample data into database  
46       mysql_db: 
47         name: testdb 
48         state: import 
49         target: /tmp/dump.sql 
50         login_user: root 
51         login_password: "{{ mysql_root_password }}"
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

SQL , .

3. SQL

. , , . , , .

dump.sql:

touch dump.sql
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

, , test INSERT:

CREATE TABLE IF NOT EXISTS test (           
  message varchar(255) NOT NULL         
  ) ENGINE=MyISAM DEFAULT CHARSET=utf8;         
  INSERT INTO test(message) VALUES('Ansible To Do List');
  INSERT INTO test(message) VALUES('Get ready');         
  INSERT INTO test(message) VALUES('Ansible is fun')
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

4. Ansible

, ansible-playbook. , , :

ansible-playbook -i hosts db-server-playbook.yml
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

, , .

5.

, . MySQL, testdb, , , test, Ansible:

mysql -u root -h localhost -p Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g. 
Your MySQL connection id is 17 
Server version: 8.0.22-0ubuntu0.20.04.3 (Ubuntu)  mysql> use testdb; 
Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A  Database changed 
mysql> show tables; 
+------------------+ 
| Tables_in_testdb | 
+------------------+ 
| test             | 
+------------------+ 
1 row in set (0.00 sec)  mysql> select * from test; 
+--------------------+ 
| message            | 
+--------------------+ 
| Ansible To Do List | 
| Get ready          | 
| Ansible is fun     | 
+--------------------+ 
3 rows in set (0.00 sec)
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

. , ? Ansible — .

Practical Ansible: Configuration Management from Start to Finish , Ansible.